Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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!
0
anonwig
Asked:
anonwig
  • 10
  • 9
1 Solution
 
SharathData EngineerCommented:
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
 
anonwigAuthor Commented:
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
 
SharathData EngineerCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
anonwigAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
SharathData EngineerCommented:
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
 
anonwigAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
anonwigAuthor Commented:
Excuse me - they both work - it's just the second doesn't output the actual rows when run in phpmyadmin - thanks Sharath!
0
 
anonwigAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
SharathData EngineerCommented:
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
 
anonwigAuthor Commented:
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
 
SharathData EngineerCommented:
Ok, you can use the non dynamic version.
0
 
anonwigAuthor Commented:
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
 
anonwigAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
anonwigAuthor Commented:
WOW SQL really is powerful! Bloody complex though!

Thank you so much - you've been fantastic :)
0
 
SharathData EngineerCommented:
Glad I could help you.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now