Combining two tables by means of another one

Question

I have a typical N-M relationship structure like this:

two independent tables:

Table Client:
id | name
1  | Joe
2  | Marc

and

Table Product:
id | name
1  | Toyota
2  | Ford
3  | Fiat

and a linking table like:

Table Purchase:
idClient | idProduct | Paid
1        | 1         | 3000
1        | 2         | 2999
2        | 3         | 4500
2        | 1         | 1000

I would like a query that produces:

client | Toyota | Ford | Fiat
Joe    |  3000  | 2999 | 
Marc   |  1000  |      | 4500

is there some way to do it in MySQL?

Thanks !

Answer 1

Like @StuartAinsworth said, you need to do a Pivot Query.

---

For a dynamic approach use this:

create table Client (
id int,
name varchar(10)
)

insert into Client values (1, 'Joe')
insert into Client values (2, 'Marc')

create table Product (
id int,
name varchar(10)
)

insert into Product values (1, 'Toyota')
insert into Product values (2, 'Ford')
insert into Product values (3, 'Fiat')

create table Purchase (
idClient int,
idProduct int,
Paid int
)

insert into Purchase values (1, 1, 3000)
insert into Purchase values (1, 2, 2999)
insert into Purchase values (2, 3, 4500)
insert into Purchase values (2, 1, 1000)


declare @cmd varchar(2048)
declare @prod varchar(10), @count int, @total int

select @cmd = 'select c.name, '

select @count = 1, @total = max(id) from Product

while @count <= @total
begin
    select @prod = name from Product where id = @count

    if(@count <> @total)
    begin
        select @cmd = @cmd + 'sum(CASE WHEN p.name = "' + @prod + '" THEN u.Paid ELSE 0 END) as "' + @prod + '", ' 
    end
    else   
    begin
        select @cmd = @cmd + 'sum(CASE WHEN p.name = "' + @prod + '" THEN u.Paid ELSE 0 END) as "' + @prod + '" ' 
    end
    select @count = @count + 1
end

select @cmd = @cmd + 'from Client c ' +
 + 'inner join Purchase u on u.idClient = c.id ' +
 + 'inner join Product p on p.id = u.idProduct ' +
 + 'group by c.name'


exec(@cmd)

drop table Client
drop table Product
drop table Purchase

Answer 2

You're trying to do a PIVOT query; there are lots of examples on stackoverflow. Search Pivot and MySQL.

For example, here's one using CASE statements: SQL query to pivot a column using CASE WHEN