MysQL Query for Master table & child table

[phpasks]

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?????

I have used below query
SELECT tbltest.test_id
FROM tbltest, tbltest_hst
where (tbltest_hst.test_id NOT IN (tbltest.test_id))

This query does not work properly.

How to solve this problem??????????

[woiwky]

I would personally use:

SELECT tbltest.test_id
FROM tbltest, tbltest_hst
WHERE tbltest_hst.test_id = tbltest.test_id

But your query should work too if you add DISTINCT:

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

[phpasks]

I would personally use:

SELECT tbltest.test_id
FROM tbltest, tbltest_hst
WHERE tbltest_hst.test_id = tbltest.test_id

But your query should work too if you add DISTINCT:

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

This is not right query.
[IMG]file:///C:/DOCUME%7E1/JIMIT%7E1.BAG/LOCALS%7E1/Temp/moz-screenshot.jpg[/IMG][IMG]file:///C:/DOCUME%7E1/JIMIT%7E1.BAG/LOCALS%7E1/Temp/moz-screenshot-1.jpg[/IMG]This out put is unique value but this value come in master table as well as same as child table.

I have needed out put this way.
Child table value come but not in master table this id used.

[woiwky]

Oh, sorry. I misunderstood you. This should get you the records in the child table which aren't in the master table:

SELECT test_id
FROM tbltest_hst
WHERE (test_id NOT IN (SELECT test_id FROM tbltest))

[phpasks]

Oh, sorry. I misunderstood you. This should get you the records in the child table which aren't in the master table:

SELECT test_id
FROM tbltest_hst
WHERE (test_id NOT IN (SELECT test_id FROM tbltest))

Thanks woiwky,

But i know inner query using this solution.

With out inner join possible or not.

[woiwky]

Without a JOIN or subquery, I don't think it can be done.

[phpasks]

Without a JOIN or subquery, I don't think it can be done.

Without inner query, because inner query very heavy query.

How much load for that inner query?
Do u know?

[woiwky]

You'll probably be better off using an left join actually. Joins are usually faster than a subquery. But you may not notice much of a difference unless you have a lot of records. I can't say what the load would be though. Check the execution time on this query:

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

If the result set is very large, then you may be able to increase the performance with a temporary table.

[phpasks]

You'll probably be better off using an left join actually. Joins are usually faster than a subquery. But you may not notice much of a difference unless you have a lot of records. I can't say what the load would be though. Check the execution time on this query:

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

If the result set is very large, then you may be able to increase the performance with a temporary table.

thanks buddy.

thanks lot