SQL: problem with a complex query

Or at least it's complex for me, as I'm a total noob with SQL.

I'd like to write a query for my MySQL-ODBC phonebook program, that returns certain details of a person and his primary phone number if there's one.

Persons and numbers are stored in different tables (persons and numbers), while their relations are in the npr (number-person relations) table.

The primary key of 'persons' is 'nick', for 'numbers' it's 'number', for 'npr' it's 'persons_nick' and 'numbers_number' together.

If a number is primary, it's signalled by the word "primary" being somewhere in the type attribute of the npr table. (I know it's primitive, but I didn't think I'd need this attribute and now I don't have time to implement it properly)

Here's the code as it is now:开发者_如何学JAVA

SELECT persons.nick AS nick, persons.fullname AS fullname,
    persons.prefix AS prefix, persons.surname AS surname,
    persons.forename AS forename, persons.photo AS photo,
    numbers.prettynumber AS primarynumber
FROM persons
RIGHT JOIN npr ON npr.persons_nick=persons.nick
LEFT JOIN numbers ON npr.numbers_number=numbers.number
WHERE npr.type LIKE '%primary%'
ORDER BY nick;

This, of course doesn't return anything if the person doesn't have a primary phone number. How could I rewrite this query to return the person's attributes and a void number in that case?

Thank you.

The solution for your problem is to move the LIKE constraint into the predicate of the left join:

SELECT persons.nick AS nick, persons.fullname AS fullname,
  persons.prefix AS prefix, persons.surname AS surname,
  persons.forename AS forename, persons.photo AS photo,
  numbers.prettynumber AS primarynumber
FROM persons
RIGHT JOIN npr ON npr.persons_nick=persons.nick
LEFT JOIN numbers ON npr.numbers_number=numbers.number 
                     and
                     npr.type LIKE '%primary%'
ORDER BY nick;

The WHERE predicate filters all records but the join predicate belongs only to this left join. If the right side doesn't match, then you still get the left side of the join.

Update It seems to me that it would be better to only use left joins in this case. I don't know the exact table definitions, but I assume that table npr is only for the tagged association between persons and numbers. So if you use only left joins then you can be sure, you get all persons whether or not they have a primary number.

You can use IsNull or Coalesce in that situation.

this is caused by your where clause, because there's no type to match it will be filtered out. You can probably fix it by doing the where something like

WHERE npr.type is null or npr.type LIKE '%primary%' 

or if npr.type can also be null in the table, you could do

WHERE npr.persons_nick is null or npr.type LIKE '%primary%' 

since it won't have an npr the linked value should be null, also do a left join on the NPR (not sure if that matters though, I never use right joins myself)