cancel
Showing results for 
Search instead for 
Did you mean: 

help with SQL Syntax

N/A

help with SQL Syntax

I've forgotten so much SQL! Please help.

I've got a hole in my database and I want to select id from table1 where there is no corresponding row (or rows) on table2.

Help!
5 REPLIES
N/A

RE: help with SQL Syntax

> I've forgotten so much SQL! Please help.
>
> I've got a hole in my database and I want to select id from table1 where there is no corresponding row (or rows) on table2.
>
> Help!

The best solution would be to use a sub query or possibly one of the UNION/INTERSECT type operators but those aren't implemented in mysql 3.x

I'm assuming you have a field to join table1 and table2 together. The simplest solution is to perform an INNER JOIN which will only display the ids where the same key exists in table1 and table2.

The other approach is to perform a LEFT JOIN and show both the id from table1 and table2, in this case all rows will be shown but those where theres no corresponding entry for table2 will have a blank table2.id field.

Alternatively you could return all rows from each table and then use the programming language of your choice to find the missing ids.
N/A

RE: help with SQL Syntax

It was late last night and what I thought would work didn't when I tried it. But going through the MYSQL manual today for something, displays what I originally thought of as a solution to your problem:

SELECT <fields> FROM table1
LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;

Think I may have forgotten that it's IS NULL as opposed to = NULL but there you go.


N/A

RE: help with SQL Syntax

Following the age old rule "When all else fails, read the instructions" I went to the mysql manual on the web. After a bit of digging, I struck pay dirt.

"mysql> SELECT table1.* FROM table1
-> LEFT JOIN table2 ON able1.id=table2.id
-> WHERE table2.id IS NULL;
This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL, of course"

Thanks for your help though.
N/A

RE: help with SQL Syntax

Well, look at that!

You and I posted the same solution from the same source one minute apart!
N/A

RE: help with SQL Syntax

> > I've forgotten so much SQL! Please help.
> >
> > I've got a hole in my database and I want to select id from table1 where there is no corresponding row (or rows) on table2.
> >
> > Help!
>
> The best solution would be to use a sub query or possibly one of the UNION/INTERSECT type operators but those aren't implemented in mysql 3.x
>
> I'm assuming you have a field to join table1 and table2 together. The simplest solution is to perform an INNER JOIN which will only display the ids where the same key exists in table1 and table2.
>
> The other approach is to perform a LEFT JOIN and show both the id from table1 and table2, in this case all rows will be shown but those where theres no corresponding entry for table2 will have a blank table2.id field.
>
> Alternatively you could return all rows from each table and then use the programming language of your choice to find the missing ids.

I thought it was an outer join when you displayed rows from one table with no corresponding row in another table.