Solved

SQL join query

Posted on 2011-02-24
19
255 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Excuse me - they both work - it's just the second doesn't output the actual rows when run in phpmyadmin - thanks Sharath!
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:anonwig
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ok, you can use the non dynamic version.
0
 

Author Comment

by:anonwig
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
WOW SQL really is powerful! Bloody complex though!

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

Expert Comment

by:Sharath
Comment Utility
Glad I could help you.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to count occurrences of each item in an array.
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.

762 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

9 Experts available now in Live!

Get 1:1 Help Now