phpAsks.com

MySQL for Not IN Query how it work

Posted by: Asif D. Khalyani

I have two table One is master table & second is secondary table.

Master Table
CREATE TABLE `tbltest` (
`test_id` int(5) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default '',
`lname` varchar(50) NOT NULL default '',
PRIMARY KEY (`test_id`)
)
INSERT INTO `tbltest` (`test_id`, `name`, `lname`) VALUES
(1, 'malav', 'shah'),
(2, 'rahul', 'shah'),
(3, 'kannan', 'Mopnnar'),
(4, 'Milan', 'Shah'),
(5, 'Kahan', 'Shah'),
(6, 'Kaushal', 'Patel'),
(7, 'Sweta', 'Shah'),
(8, 'Dipak', 'Jani');

COMMIT;

Secondary Table
CREATE TABLE `tbltest_hst` (
`test_hst_id` int(10) NOT NULL auto_increment,
`test_id` int(10) NOT NULL default '0',
`name` varchar(50) NOT NULL default '',
`lname` varchar(50) NOT NULL default '',
PRIMARY KEY (`test_hst_id`)
)

INSERT INTO `tbltest_hst` (`test_hst_id`, `test_id`, `name`, `lname`) VALUES
(1, 10, 'rupal', 'patel'),
(2, 1, 'malav', 'shah'),
(3, 2, 'rahul', 'shah'),
(4, 13, 'rina', 'mehta'),
(5, 11, 'viral', 'patel'),
(6, 11, 'viral', 'patel'),
(7, 17, 'i****a', 'shah'),
(8, 17, 'i****a', 'shah'),
(9, 20, 'Pooja', 'Mehta'),
(10, 23, 'Kavita', 'Patel'),
(11, 3, 'Kannan', 'Mopnnar'),
(12, 4, 'Milan', 'Shah'),
(13, 5, 'Kahan', 'Shah'),
(14, 5, 'Kahan', 'Shah'),
(15, 25, 'Chintan', 'Shah'),
(16, 26, 'Neha', 'Shah'),
(17, 25, 'Chintan', 'Shah'),
(18, 26, 'Neha', 'Shah');

COMMIT;

I have first master table tbltest & Second child table tbltest_hst

If i have 1 to 8 unique id record in master table.
I have child table 1 to 8 unique id and also deleted some id record in my child table.

How find out this not in master table record,
but my child record?????

You can use inner query or using join query

Inner Query
SELECT DISTINCT tbltest.test_id
FROM tbltest, tbltest_hst
WHERE (tbltest_hst.test_id NOT IN (tbltest.test_id))

Join Query
SELECT tbltest_hst.test_id
FROM tbltest_hst
LEFT JOIN tbltest ON tbltest_hst.test_id = tbltest.test_id
WHERE ISNULL(tbltest.test_id)

Back to Index Page