I have a database that has 2 tables with a parent-child relationship. Some of the entries in the parent do not have children. I am looking for a QUERY that would select all of the entries from the parent that does not have a child. I can find other ways of doing in php but that would require alot of work changing the whole script. So, I am looking for a mysql query, Not a php solution.
Thanks
SELECT * FROM table WHERE child=''
Cgraz
the entries in the parent-child relationship are linked by a unique identifier created by the parent autocount. That is the only thing that links them togeter. I want to query the parent table for all entries that don't have any child entries in the child table.
thanks again
oh. I'm not very experienced in multiple table queries, so I'm not sure how well this will work, but you can try
SELECT * FROM parent_table WHERE parent_table.id = child_table.id AND child_table.child_field = ''
where id is the relating field
This is asked frequently in this forum.
http://www.phpbuilder.com/board/showthread.php?threadid=10227696
Okay that left join works beautifully, thanks. I do have another question though. How could I do a count on this? This is what I have:
select count(*) as count from parenttable inner join childtable on child.id=parent.id
This gives me a number much higher than what actually displays on the screen. Any ideas?
Your query will count the child rows that have parents. If you want to count the number of parents try this (not tested):
select count(distinct parent.id) as parentcount from parenttable inner join childtable on child.id=parent.id
