Solved

SQL join query

Posted on 2011-02-24
19
267 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:anonwig
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
19 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 34972378
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

0
 

Author Comment

by:anonwig
ID: 34974617
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 :)
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34974752
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?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:anonwig
ID: 34974913
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...
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34976101
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

0
 
LVL 41

Expert Comment

by:Sharath
ID: 34976188
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

0
 

Author Comment

by:anonwig
ID: 34977232
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
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34977419
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.
0
 

Author Comment

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

Author Comment

by:anonwig
ID: 34977594
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.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34977630
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

0
 
LVL 41

Expert Comment

by:Sharath
ID: 34977637
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

0
 

Author Comment

by:anonwig
ID: 34977646
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.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34977677
Ok, you can use the non dynamic version.
0
 

Author Comment

by:anonwig
ID: 34977699
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

0
 

Author Comment

by:anonwig
ID: 34977703
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

0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 34977709
Use LEFT JOIN.
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
LEFT 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

0
 

Author Comment

by:anonwig
ID: 34977775
WOW SQL really is powerful! Bloody complex though!

Thank you so much - you've been fantastic :)
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34977813
Glad I could help you.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

736 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question