mySQL one-to-many query

Question

I've got 3 tables that are something like this (simplified here ofc):

• users
  • user_id
  • user_name
• info
  • info_id
  • user_id
  • rate
• contacts
  • contact_id
  • user_id
  • contact_data

users has a one-to-one relationship with info, although info doesn't always have a related entry.

users has a one-to-many relationship with contacts, although contacts doesn't always have related entries.

I know I can grab the proper 'users' + 'info' with a left join, is there a way to get all the data I want at once?

For example, one returned record might be:

user_id: 5
user_name: tom
info_id: 1
rate: 25.00
contact_id: 7
contact_data: 555-1212
contact_id: 8
contact_data: 555-1315
contact_id: 9
contact_data: 555-5511

Is this possible with a single query? Or must I use multiple?

Answer 1

It is possible to do what you're asking in one query, but you'd either need a variable number of columns which is evil because SQL isn't designed for that, or you'd have to have a fixed number of columns, which is even more evil because there is no sensible fixed number of columns you could choose.

I'd suggest using one of two alternatives:

1. Return one row for each contact data, repeating the data in other columns:

5 tom 1 25.00 7 555-1212
5 tom 1 25.00 8 555-1315
5 tom 1 25.00 9 555-5511

The problem with this of course is that redundant data is normally a bad idea, but if you don't have too much redundant data it will be OK. Use your judgement here.

2. Use two queries. This means a slightly longer turnaround time, but less data to transfer.

In most cases I'd prefer the second solution.

You should try to avoid making a large number of queries inside a loop. This can almost always be rewritten to a single query. But if using two queries is the most natural way to solve your problem, just use two queries. Don't try to cram all the data you need into a single query just for the sake of reducing the number of queries.