SQL join query
Posted on 2011-02-24
Ok - so not quite sure how to do this:
Table 1: members (id, first_name, last_name, email_address)
Table 2: tags (tid, uid, ttype, tag)
I already have the members id's stored as a mysql query result.
What I would like to do is select the fields id, first_name, last_name from members for each of the id's I have stored, and create extra temporary columns in the members table (not actually create it - but return it in the sql results for this query as a column) for the tags of ttype 'hometown' and 'hobby'.
id: 103, first_name: paul, last_name: jones, email_address: firstname.lastname@example.org
tid:121, uid: 103, ttype: hobby, tag: skiing
tid:421, uid: 103, ttype: hobby, tag: reading
tid:323, uid: 103, ttype: hometown, tag: London, UK
The tags can have multiple data for the same uid, so the tag values of these rows should be merged into one temporary column called hobby, and one temporary column called hometown.
So the result will be a table which has the columns (id, first_name, last_name, hobby, hometown)
I hope that makes sense - any questions please ask.