JOIN question, a big trubble i think

Anon

Hello, i have 2 tables:

phones
PHONE_NO

phone_owner
PHONE_NO
USER_ID

I want to compile a query listing WHAT PHONE_NO HAS OR NOT an owner.

Something like this:

(query);
phones.PHONE_NO USER_ID
01 12
02 (empty, does not have an owner)
03 10

And So On

In short: I want to know what phones.PHONE_NO has a ROW in phone_owner (or not) 🙂

I hope you understand me

Thank you in advance.

tomhath

Depends on the dbms you are using. With most you can just do a subselect "where not exists ...". I'll assume you are using mysql (which does not support subselects) so you 'll have to do it the way the mysql manual explains it in Section 1.7.4.1 Sub-Selects:

http://www.mysql.org/documentation/mysql/bychapter/manual_Introduction.html#ANSI_diff_Sub-selects

bretticus

LEFT JOIN should work if you insert a little logic.

select PHONE_NO, USER_ID from phones AS a LEFT JOIN phone_owner ON phones.PHONE_NO = phone_owner.PHONE_NO;

should produce:

01 12
02 null
03 10

If the USER_ID value is null, do something.

bretticus

whoops, scratch the 'AS a' and add a OUTER I believe:

SELECT PHONE_NO, USER_ID FROM phones LEFT OUTER JOIN phone_owner ON phones.PHONE_NO = phone_owner.PHONE_NO;