asked on

SQL join query

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'.

Example tables:
members
id: 103, first_name: paul, last_name: jones, email_address: paul@jones.com

tags
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.

Thanks!

Sharath S

try this.

SELECT m.id,m.first_name,m.last_name,m.email_address,t.ttype,t.tag 
  FROM members m 
       JOIN (  SELECT uid,ttype,GROUP_CONCAT(tag) tag 
                 FROM tags 
             GROUP BY uid,ttype) t 
         ON m.id = t.uid;

Open in new window

anonwig

ASKER

Hi Sharath - thanks for the reply, that returns data like this:

id 	first_name 	last_name 	email_address 	ttype 	tag
46 	Bob 	Green 	fake@email.com 	book 	Moneyball,One Up On Wall Street,Rework,The Great G...
46 	Bob 	Green 	fake@email.com 	hobby 	Skiing,Film,Longboarding
46 	Bob 	Green 	fake@email.com 	hometown 	Chicago, IL
46 	Bob 	Green 	fake@email.com 	investor 	Warren Buffett,Larry Fink,Marc Andreessen
46 	Bob 	Green 	fake@email.com 	movie 	American Psycho,Avatar,Wedding Crashers,Walden,The...
46 	Bob 	Green 	fake@email.com 	philosopher 	Adam Smith,Rufino Mendoza
46 	Bob 	Green 	fake@email.com 	pitch 	ICE
46 	Bob 	Green 	fake@email.com 	sector 	Consumer Staples
46 	Bob 	Green 	fake@email.com 	television 	Conan,Entourage,Family Guy,The Simpsons

Open in new window

I would like it to display the columns:id, first_name, last_name, email_address, book, hobby, hometown, investor, movie, philosopher, pitch, sector, television.

So you get just 1 row for each user.

Thanks :)

Sharath S

All these are different ttypes? Do you want all the ttypes concatenated as one string or do you want ttype, tag combined as a string?

anonwig

ASKER

No I don't want all the ttypes as one string

I would like each ttype to have its own column, and all the tags for that ttype concatenated in that column

I thought the explanation above "I would like it to display the columns:id, first_name, last_name, email_address, book, hobby, hometown, investor, movie, philosopher, pitch, sector, television" would be pretty helpful...

Essentaially take all the hobby tags, and put them in the hobby column, etc.

That way just 1 row for each user...

Sharath S

One way is hard coding all the values.

SELECT m.id, 
         m.first_name, 
         m.last_name, 
         m.email_address, 
         MAX(CASE 
               WHEN t.ttype = 'book' THEN t.tag 
             END) AS book, 
         MAX(CASE 
               WHEN t.ttype = 'hobby' THEN t.tag 
             END) AS hobby, 
         MAX(CASE 
               WHEN t.ttype = 'hometown' THEN t.tag 
             END) AS hometown, 
         MAX(CASE 
               WHEN t.ttype = 'investor' THEN t.tag 
             END) AS investor, 
         MAX(CASE 
               WHEN t.ttype = 'movie' THEN t.tag 
             END) AS movie, 
         MAX(CASE 
               WHEN t.ttype = 'philospoher' THEN t.tag 
             END) AS philospoher, 
         MAX(CASE 
               WHEN t.ttype = 'pitch' THEN t.tag 
             END) AS pitch, 
         MAX(CASE 
               WHEN t.ttype = 'sector' THEN t.tag 
             END) AS sector, 
         MAX(CASE 
               WHEN t.ttype = 'television' THEN t.tag 
             END) AS television 
    FROM members m 
         JOIN (  SELECT uid, 
                        ttype, 
                        GROUP_CONCAT(tag) tag 
                   FROM tags 
               GROUP BY uid, 
                        ttype) t 
           ON m.id = t.uid 
GROUP BY m.id, 
         m.first_name, 
         m.last_name, 
         m.email_address;

Open in new window

Sharath S

If you don't know the number of ttypes, you can go for dynamic sql.

select group_concat(distinct 'MAX(CASE WHEN t.ttype = ''',ttype,''' THEN t.tag END) AS ',ttype) into @sql from tags;
select concat('SELECT m.id, 
         m.first_name, 
         m.last_name, 
         m.email_address,',@sql,' FROM members m 
         JOIN (  SELECT uid, 
                        ttype, 
                        GROUP_CONCAT(tag) tag 
                   FROM tags 
               GROUP BY uid, 
                        ttype) t 
           ON m.id = t.uid 
GROUP BY m.id, 
         m.first_name, 
         m.last_name, 
         m.email_address;') into @sql;
  
PREPARE stmt FROM @sql; 

EXECUTE stmt;

+------+------------+-----------+----------------+----------------+-----------+
| id   | first_name | last_name | email_address  | hobby          | hometown  |
+------+------------+-----------+----------------+----------------+-----------+
|  103 | paul       | jones     | paul@jones.com | skiing,reading | London,UK |
+------+------------+-----------+----------------+----------------+-----------+
1 row in set (0.01 sec)

Open in new window

anonwig

ASKER

Hi Sharath - your solution at 5:25pm does the job excellently - so consider points awarded.

I am curious about the dynamic solution also - it returns no results however...

Best,
Ben

Sharath S

I have tested both solutions and got the result. Did you check all the column names and table names in the dynamic SQL? Could you run select @SQL and post the query here. Will check if I miss anything in the dynamic SQL.

anonwig

ASKER

Excuse me - they both work - it's just the second doesn't output the actual rows when run in phpmyadmin - thanks Sharath!

anonwig

ASKER

Final question - if I want to limit the results to a list of member ids (so the id column in the member field) how best to do this - bear in mind it could be quite a long list of member ids.

Sharath S

To limit the member ids, you can add a WHERE clause.

WHERE m.id IN (100,200,300)

SELECT m.id, 
         m.first_name, 
         m.last_name, 
         m.email_address, 
         MAX(CASE 
               WHEN t.ttype = 'book' THEN t.tag 
             END) AS book, 
         MAX(CASE 
               WHEN t.ttype = 'hobby' THEN t.tag 
             END) AS hobby, 
         MAX(CASE 
               WHEN t.ttype = 'hometown' THEN t.tag 
             END) AS hometown, 
         MAX(CASE 
               WHEN t.ttype = 'investor' THEN t.tag 
             END) AS investor, 
         MAX(CASE 
               WHEN t.ttype = 'movie' THEN t.tag 
             END) AS movie, 
         MAX(CASE 
               WHEN t.ttype = 'philospoher' THEN t.tag 
             END) AS philospoher, 
         MAX(CASE 
               WHEN t.ttype = 'pitch' THEN t.tag 
             END) AS pitch, 
         MAX(CASE 
               WHEN t.ttype = 'sector' THEN t.tag 
             END) AS sector, 
         MAX(CASE 
               WHEN t.ttype = 'television' THEN t.tag 
             END) AS television 
    FROM members m 
         JOIN (  SELECT uid, 
                        ttype, 
                        GROUP_CONCAT(tag) tag 
                   FROM tags 
               GROUP BY uid, 
                        ttype) t 
           ON m.id = t.uid 
WHERE m.id IN (100,200,300)
GROUP BY m.id, 
         m.first_name, 
         m.last_name, 
         m.email_address;

Open in new window

Sharath S

Regarding dynamic sql, could you run this and post the SQL statement?

select group_concat(distinct 'MAX(CASE WHEN t.ttype = ''',ttype,''' THEN t.tag END) AS ',ttype) into @sql from tags;
select concat('SELECT m.id, 
         m.first_name, 
         m.last_name, 
         m.email_address,',@sql,' FROM members m 
         JOIN (  SELECT uid, 
                        ttype, 
                        GROUP_CONCAT(tag) tag 
                   FROM tags 
               GROUP BY uid, 
                        ttype) t 
           ON m.id = t.uid 
GROUP BY m.id, 
         m.first_name, 
         m.last_name, 
         m.email_address;') into @sql

SELECT @sql;

Open in new window

anonwig

ASKER

The non dynamic version is perfect for me - because I'm running a database search script and it lets me control the user input by specifying an array of acceptable fields (so for example they can't pull out the passwords!)

Thank you v much for your help - excellent, really.

Sharath S

Ok, you can use the non dynamic version.

anonwig

ASKER

Sorry 1 final problem - hopefully minor.

It currently only includes members in the results, for which there is an entry with their member id in the tags field - many members won't have tags entries - how can I get them to show up?

Thanks,
Ben

I enclose an example SQL statement generated by my script:

SELECT m.id, m.first_name, m.last_name, m.email_address, MAX(CASE WHEN t.ttype = 'hometown' THEN t.tag END) AS hometown FROM members m JOIN ( SELECT uid, ttype, GROUP_CONCAT(tag) tag FROM tags GROUP BY uid, ttype) t ON m.id = t.uid WHERE m.id IN (1,3,4,5,6,7,8,9,10,11,12,13,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,40,41,42,44,45,46,47,48,49,50,51,52,53,54,55,56,57,64,66,68,69,70,71,73,74,75,77,79,81,82,83,90,91) GROUP BY m.id, m.first_name, m.last_name, m.email_address

Open in new window

anonwig

ASKER

Sorry - made it clearer:

SELECT m.id, m.first_name, m.last_name, m.email_address, MAX(
CASE WHEN t.ttype = 'hometown'
THEN t.tag
END ) AS hometown
FROM members m
JOIN (

SELECT uid, ttype, GROUP_CONCAT( tag ) tag
FROM tags
GROUP BY uid, ttype
)t ON m.id = t.uid
WHERE m.id
IN ( 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 40, 41, 42, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 64, 66, 68, 69, 70, 71, 73, 74, 75, 77, 79, 81, 82, 83, 90, 91 )
GROUP BY m.id, m.first_name, m.last_name, m.email_address
LIMIT 0 , 30

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anonwig

ASKER

WOW SQL really is powerful! Bloody complex though!

Thank you so much - you've been fantastic :)

Sharath S

Glad I could help you.