0

I'm trying to select records from 3 tables using a JOIN which is working well but I now need to add some additional conditions to the SELECT which I can't figure out. My current select works like this:

SELECT user.*, address.*, state.* FROM user 
LEFT JOIN address ON user.id = address.user_id 
LEFT JOIN state ON user.id = state.user_id

Now, the address and state could have multiple records but both tables have a bit field to indicate that they are the primary record and that's the one I want to select. I have tried adding a WHERE address.state = b'1' to the statement but it only returns those records that have a primary record, what I would ideally like to do is select the top record from the address/state table sorted by the primary field so the primary will be selected if it exists but the next record will be selected if there is no primary.

How can I achieve this with MySQL 5?

JWood
  • 2,804
  • 2
  • 39
  • 64

1 Answers1

3

Add the condition to your LEFT JOIN:

LEFT JOIN address ON ( user.id = address.user_id AND address.state = b'1' )

The left join will only return rows that match this criteria, but you will still get users without a primary address.

Peter Lang
  • 54,264
  • 27
  • 148
  • 161
  • Excellent, thanks! That is a step in the right direction. Is there a way to order the JOIN'd results rather than have a WHERE clause? So if a user has a primary address always pick that but if not just pick the next one primary or not? – JWood Jul 13 '12 at 13:44
  • @JWood Yes - see here: http://stackoverflow.com/questions/10999913/why-do-results-from-a-sql-query-not-come-back-in-the-order-i-expect – David Manheim Jul 13 '12 at 15:08
  • 1
    Unless I misunderstood, and you are asking to pick the match ranked highest, in which case you need to write a subquery. You should ask that as a new question. – David Manheim Jul 13 '12 at 15:09
  • I think I do need a subquery in this case as ORDER only works on the primary table. I'll ask another questions. Thanks – JWood Jul 16 '12 at 18:54