Selecting two tables where the columns are not always equal

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
enatefox
Forum Newbie
Posts: 11
Joined: Fri Apr 04, 2008 12:58 pm

Selecting two tables where the columns are not always equal

Post 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?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

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

Post by onion2k »

You're aliasing tbl2 to 'a' rather than 'b'. That might not be the problem, but you should fix it anyway.
phpMonk
Forum Newbie
Posts: 5
Joined: Sun Aug 17, 2008 6:23 am

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

Post by phpMonk »

Use a LEFT join
Post Reply