Negative join???

Anon

I have two tables in a MySQL data base-

(agency table)

id

1
2
3
4

(banner table)

id agencyid

1 3
2 3
3 1

SELECT DISTINCT a.id FROM agency AS a, banner AS b WHERE a.id=b.agencyid;

Works great for extracting agencies with banners, but I also want to select all agencies that DO NOT have banners (and do it in SQL rather than in PHP).

SELECT a.id FROM agency AS a, banner AS b WHERE a.id!=b.agencyid;

Does not work, and I've tried various joins, all without success. Is this possible in SQL?

tasistro

Try:

SELECT a.id FROM agency AS a
LEFT JOIN banner AS b
ON a.id=b.agencyid
WHERE b.agencyid IS NULL

This should work because the join operation will bring agency and banner together, if a row in agency finds NO corresponding row in banner the all banner fields for that row are set to NULL. In that case b.agencyid should be NULL so WHERE b.agencyid IS NULL evaluates to TRUE and thus the row is returned.

[deleted]

Correct.

Just FYI Patrick, simple tricks like this are explained by the MySQL manual, I suggest you have a look at it, it might give you new ideas to approach your problems.

smarlowe

The common way to do this in "SQL" (not mysql's version of SQL) is either with an except or a not in:

select id from test except (select id from test2);

select id from test where id not in (select id from test2);

will select all the rows in test that do not have a matching id in test2.

Of course, mysql has doesn't have subqueries (yet) so you'll have to do what Gerardo and vincent have told you.

Anon

Yes, I found that in an SQL book I have, but like you said, MySQL does not yet support subqueries. I don't think there's any way to do this in MySQL and I'll have to use perl or php to process the results.

Thanks for the advice.

Anon

I've been through the manual many times, and also two books on MySQL and at this point, I don't think you can do this with MySQL. I found reference to it in a generic SQL book, but MySQL doesn't support subqueries, which is what will work. Looks like I'll just have to process the results in the script rather than using SQL.

Anon

Unfortunately, the banner.agencyid column does not have any NULL values in it. Looks like I'll have to process the results in my script rather than using SQL to get what I want (at least until and if MySQL adds subqueriries).

Thanks

[deleted]

You miss the point.

When MySQL (or any database for that matter) does a LEFT JOIN on two tables, it will match the records from the first table up with the records from the right depending on the 'ON' clause

SELECT column
FROM table1 LEFT JOIN table2
WHERE table1.column = table2.column

If there are no records in table2 to match the value in table1, then the database just puts NULL values in it's place.

So if you select the results where the columns from the right table are all NULL then you are selecting all the records from table1 that have no match in table2.

Simple and effective.