CODEWITHSUNDEEP
mysql
Matt Smith
Matt Smith

Reputation: 39

MySQL - Join row of one table as a column of another

Really hoping someone can help with this. I'm sure there are better ways to structure my data, but due to time constraints it would be ideal to have a solution for the current structure if possible.

I have 2 tables with the following structure:

Table 1

id  field1
1   quest1  
2   quest2
3   quest3

Table 2

id  uid  quest1  quest2  quest3
1   18   yes     no      yes
2   27   yes     no      no

Basically, I need to return table 1 with a row of table 2 (based upon uid) in the following format:

id  field1  field2
1   quest1  yes
2   quest2  no
3   quest3  yes

Is this possible at all? Looking online I keep coming across Pivot Tables and Crosstab Queries, but they don't seem to be able to achieve what I'm after...unless I'm wrong? (which I probably am lol).

Appreciate any help anyone can give for this.

Matt

Upvotes: 2

Views: 2324

Answers (3)

eggyal
eggyal

Reputation: 125835

SELECT t1.*, CASE t1.field1
  WHEN 'quest1' THEN t2.quest1
  WHEN 'quest2' THEN t2.quest2
  WHEN 'quest3' THEN t2.quest3
END AS field2
FROM t1, t2
WHERE t2.uid = 18;

See it on sqlfiddle.

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

select t1.id, t2.field1, t2.field2 
from table1 t1
inner join (
    select id, 'quest1' as field1, quest1 as field2 from table2
    union all
    select id, 'quest2' as field1, quest2 as field2 from table2
    union all
    select id, 'quest3' as field1, quest3 as field2 from table2
) t2u on t1.id = t2.id
where t2.uid = 18

Upvotes: 0

Tomas
Tomas

Reputation: 59435

MySQL doesn't support pivot tables/crosstab queries.

It has been sometimes criticized for that but the defense is that it's a feature, not deficiency :-) Pivot queries belong more to the presentation layer, as what they create is not a table in database sense. So they more belong to the applictation layer that extracts the data from the database.

Upvotes: 2

Related Questions