Solved

SQL join query

Posted on 2011-02-24
19
257 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
  • 10
  • 9
19 Comments
 
LVL 40

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 40

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
 

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 40

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 40

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 40

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 40

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 40

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 40

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 40

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 40

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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.

914 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now