CODEWITHSUNDEEP
mysqljoin
deb0rian
deb0rian

Reputation: 976

MySQL join and fields from the second table as columns in result

I was wondering if my approach is adequate, possible and how would I do it?

Consider you have a 2 MySQL tables:

TABLE A

ID   alias     
--   --
11   banner_a
26   banner_b

TABLE B

Column  Type
pid         int(10) 
lang    varchar(2)  
field   varchar(255)    
value   text

And example data for TABLE B:

id  lang    field               value

26  da      banner_type         single
26  da      priority            5
26  da      timing              5

26  de      banner_type         single
26  de      priority            1
26  de      timing              10

So, assuming I'd like to select all/some records from TABLE A, and their related fields with values from TABLE B, grouped by lang. Where field from table B is selected like a regular column in MySQL, and it's value would be field's value.

Expecting to see results like this:

result record 1:

id 26
alias banner_b
lang da
banner_type single
priority 5
timing 5

result record 2:

id 26
alias banner_b
lang de
banner_type single
priority 5
timing 5

I did something like that a few years ago, I think with COALESCE, but it's not what I want.

Where should I start if at all? :-)

Thanks

Upvotes: 0

Views: 260

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79949

Try this:

SELECT
  b.id, 
  b.lang,
  MAX(CASE WHEN b.field = 'banner_type' THEN b.value END) AS 'banner_type',
  MAX(CASE WHEN b.field = 'priority' THEN b.value END) AS 'priority',
  MAX(CASE WHEN b.field = 'timing' THEN b.value END) AS 'timing'
FROM TableA a
INNER JOIN TableB b ON a.ID = b.ID
GROUP BY b.id, b.lang;

SQL Fiddle Demo

This will give you:

| ID | LANG | BANNER_TYPE | PRIORITY | TIMING |
-----------------------------------------------
| 26 |   da |      single |        5 |      5 |
| 26 |   de |      single |        1 |     10 |

Note that: In the query I used MAX. Because, each group id lang in the table TableB has two values for each BANNER_TYPE | PRIORITY | TIMING so which value do you want for each group?

Upvotes: 1

Related Questions