Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to fetch some persons data from two tables via equi join. Some persons have address data and some have not. For those persons who haven't the addresses_id from table persons is NULL.

For all persons who have an address I do (query#1):

SELECT p.last_name, p.first_name, 
       a.street, a.house_nr, a.post_code, a.city 
  FROM persons AS p, addresses AS a 
 WHERE p.id=42 AND a.id=p.addresses_id;

For all persons without address data I do (query#2):

SELECT p.last_name, p.first_name 
  FROM persons AS p WHERE p.id=42;

Is there any smooth way to put these two queries into one. Maybe with an if-clause? I don't want to check the addresses_id field before by myself and then execute query#1 or query#2.

I'm using MySQL with InnoDB engine. Thanks.

share|improve this question
    
Use an outer join –  Phil Apr 8 '12 at 21:30
    
Do you have an example for me? –  arminb Apr 8 '12 at 21:34

1 Answer 1

up vote 6 down vote accepted

Maybe something like this:

SELECT 
  p.last_name, 
  p.first_name, 
  a.street, 
  a.house_nr, 
  a.post_code, 
  a.city 
FROM persons AS p
LEFT JOIN addresses AS a 
WHERE p.id=42 
share|improve this answer
    
Thanks. Really simple, outer join was the keyword I was looking for. –  arminb Apr 9 '12 at 0:22
    
: No problem.. Rememberto upvote if you think the answer is good. It gives us all warm-fuzzy feelings :P –  Arion Apr 9 '12 at 0:35
    
I tried bro but I haven't enough rep ;) –  arminb Apr 9 '12 at 16:53
    
No problem mate. Glad to help you... :-) –  Arion Apr 9 '12 at 18:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.