2

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.

Improve this question
2
  • 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
6

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
Improve this answer
3
  • Thanks. Really simple, outer join was the keyword I was looking for. – arminb Apr 9 '12 at 0:22
  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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