Page 1 of 1

Selecting two tables where the columns are not always equal

Posted: Mon Aug 18, 2008 10:43 am
by enatefox
Hello,

I have a few tables with various fields I need to update so I am making a view that will be updated to handle where each column is for me.

So the join is on two columns:
enc_id and person_id

The layout needs to be like so:

Code: Select all

 
SELECT 
a.col1, a.col2, a.col3, 
b.col1, b.col2, b.col3
FROM 
tbl1 a, tbl2 a
WHERE 
a.enc_id = b.enc_id AND a.person_id = b.person_id
 
This would be simple but tbl2 (b) doesn't always have either of those fields filled in.
So if they aren't empty, they will equal but if they are empty then it should just return NULL for that table's columns, right?

I get 0 results returned but if I leave of tbl2 completely I get tbl1 as expected.
How do I get it to join on the columns if the row exists in tbl2 and otherwise return NULL for that table?

Re: Selecting two tables where the columns are not always equal

Posted: Mon Aug 18, 2008 10:48 am
by onion2k
You're aliasing tbl2 to 'a' rather than 'b'. That might not be the problem, but you should fix it anyway.

Re: Selecting two tables where the columns are not always equal

Posted: Mon Aug 18, 2008 12:15 pm
by phpMonk
Use a LEFT join