How to perform these joins with MySQL?

Question

Having these tables:

clients
-------------------
id
name
town

companies
------------------
id
name

credits
------------------
clients_id
companies_id
credit

Example:

clients
-------------
1  john doe   London
2  jane smith Paris

companies
-------------
1  mycompany1
2  mycompany2

credits
-------------
1  1   5000
1  2   3250
2  2   4500

How can I list all the clients including an additional column for each credit she has in a company? I also need to alias each of these columns in this fashion: "credit_"

Something like:

id  name        town    credit_1  credit_2
1   john doe    London  5000      3250
2   jane smith  Paris   NULL      4500

I'm not sure if doing two LEFT JOINS to credits would work.

And what have you tried? You must have at least a partial solution. — Gordon Linoff, Nov 24 '16 at 14:22
What's the expected result if john doe suddenly gets another credit? — jarlh, Nov 24 '16 at 14:22
@GordonLinoff: I'm totally lost about this. Nothing I've done gets close to what I need. — luis.ap.uyen, Nov 24 '16 at 14:28
That's call to _pivot_ a table. You should do a search on this term. — Little Santi, Nov 24 '16 at 14:29
@jarlh One client can only have a unique credit from a company. He couldn't have more credits in the example, just an update in the ones he has. — luis.ap.uyen, Nov 24 '16 at 14:31
Generally you are looking for PIVOT in MySQL. Check this http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns — Viki888, Nov 24 '16 at 14:34
Wow, looks kind of complex that PIVOT solution even though the problem seemed easy to tackle at first. Thank you! — luis.ap.uyen, Nov 24 '16 at 14:40

How to perform these joins with MySQL?

0 Answers0