Solved

MySQL - ned to join multiple tables

Posted on 2011-09-11
13
303 Views
Last Modified: 2012-05-12
Hi,
I have a MySQL select query below which returns the results I need from my user_upd_cupid_tags table, but how can I combine this to also retrieve the corresponding user details from my users & profile table?

SELECT DISTINCT tags.user_id AS id FROM user_upd_cupid_tags as tags WHERE tags.cupid_tag IN (2000,2012,2024,2027,2039)

How do I join this to also obtain the “username”, “email” & “contact” details from my users table where id's returned from above matche “id” in my users table
and also “countryLive”, “stateLive” & “cityLive” from my “profile” table where id's returned above match “user_id” in my profile table

I hope this makes sense.

Thanks in advance for your help.
0
Comment
Question by:sabecs
  • 6
  • 6
13 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 36520466
Looks like nothing more than an ordinary join to me.  I guess you're a newbie in SQL?  I'd rather give you links that will teach you about joins:

http://www.halfgaar.net/sql-joins-are-easy
http://www.roseindia.net/sql/sqljoin/mysql-join-3-tables.shtml
http://mysqljoin.com/
0
 

Author Comment

by:sabecs
ID: 36520786
Thanks johanntagle for the links.
I have performed joins before with multiple tables but I don't know what I am doing wrong here, I keep getting SQl errors or thousands of duplicate records.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36520850
Post your trial SQL statements here and let's see what's wrong with them.
0
 

Author Comment

by:sabecs
ID: 36520873
Thanks johanntagle for your help.

SELECT DISTINCT tags.user_id AS id FROM user_upd_cupid_tags as tags WHERE tags.cupid_tag IN (2000,2012,2024,2027,2039) LEFT JOIN user AS u ON tags.user_id = u.id
0
 

Author Comment

by:sabecs
ID: 36520882
This one seems to produce what I need but it still has duplic id's ?

SELECT
u.id AS id,
u.sess_id AS sess_id,  
u.username AS username,
u.session_last_active AS last_active,
u.email AS email,
u.contact AS contact,
profile.countryLive AS country,
profile.stateLive  AS state,
profile.cityLive AS city,
profile.updated AS profile_upd,
profile.birthdate_day AS birthdate_day,
profile.birthdate_month AS birthdate_month,
profile.birthdate_year AS birthdate_year,
tags.cupid_tag AS cupid_tag,
profile.gender AS gender,
profile.updated AS profile_upd,
photos.image1 AS image1,
photos.updated AS photos_upd
FROM users AS u
INNER JOIN user_upd_cupid_tags AS tags, user_upd_my_profile AS profile, user_upd_photos AS photos
WHERE tags.cupid_tag IN (2000,2012,2024,2027,2039)
AND id = tags.user_id
AND id = profile.user_id
AND id = photos.user_id
ORDER BY id
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36520883
Can't tell where that will go wrong except you said you want to get other columns in users so:

select distinct u.id, u.username, u.email, u.contact
from users u join tags t on (t.user_id=u.id)
where t.cupid_tag in (2000,2012,2024,2027,2039);

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 24

Expert Comment

by:johanntagle
ID: 36520885
This one seems to produce what I need but it still has duplic id's ?

SELECT
u.id AS id,
u.sess_id AS sess_id,  
u.username AS username,
u.session_last_active AS last_active,
u.email AS email,
u.contact AS contact,
profile.countryLive AS country,
profile.stateLive  AS state,
profile.cityLive AS city,
profile.updated AS profile_upd,
profile.birthdate_day AS birthdate_day,
profile.birthdate_month AS birthdate_month,
profile.birthdate_year AS birthdate_year,
tags.cupid_tag AS cupid_tag,
profile.gender AS gender,
profile.updated AS profile_upd,
photos.image1 AS image1,
photos.updated AS photos_upd
FROM users AS u
INNER JOIN user_upd_cupid_tags AS tags, user_upd_my_profile AS profile, user_upd_photos AS photos
WHERE tags.cupid_tag IN (2000,2012,2024,2027,2039)
AND id = tags.user_id
AND id = profile.user_id
AND id = photos.user_id
ORDER BY id

Try putting a DISTINCT in the SELECT clause
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36520888
SELECT
u.id AS id,
u.sess_id AS sess_id,  
u.username AS username,
u.session_last_active AS last_active,
u.email AS email,
u.contact AS contact,
profile.countryLive AS country,
profile.stateLive  AS state,
profile.cityLive AS city,
profile.updated AS profile_upd,
profile.birthdate_day AS birthdate_day,
profile.birthdate_month AS birthdate_month,
profile.birthdate_year AS birthdate_year,
tags.cupid_tag AS cupid_tag,
profile.gender AS gender,
profile.updated AS profile_upd,
photos.image1 AS image1,
photos.updated AS photos_upd
FROM users AS u
INNER JOIN user_upd_cupid_tags AS tags
On id = tags.user_id
INNER JOIN  user_upd_my_profile AS profile
on id = profile.user_id
Inner join user_upd_photos AS photos
on id = photos.user_id
WHERE tags.cupid_tag IN (2000,2012,2024,2027,2039)

ORDER BY id

0
 

Author Comment

by:sabecs
ID: 36520889
Also, user_upd_cupid_tags table may contain more than one row for each user id with a different cupid_tag, but I only want one record returned per user id.
0
 

Author Comment

by:sabecs
ID: 36520906
Thanks, I have tried with DISTICT but still get duplicate id's?

SELECT DISTINCT
u.id AS id,
u.sess_id AS sess_id,  
u.username AS username,
u.session_last_active AS last_active,
u.email AS email,
u.contact AS contact,
profile.countryLive AS country,
profile.stateLive  AS state,
profile.cityLive AS city,
profile.updated AS profile_upd,
profile.birthdate_day AS birthdate_day,
profile.birthdate_month AS birthdate_month,
profile.birthdate_year AS birthdate_year,
tags.cupid_tag AS cupid_tag,
profile.gender AS gender,
profile.updated AS profile_upd,
photos.image1 AS image1,
photos.updated AS photos_upd
FROM users AS u
INNER JOIN user_upd_cupid_tags AS tags, user_upd_my_profile AS profile, user_upd_photos AS photos
WHERE tags.cupid_tag IN (2000,2012,2024,2027,2039)
AND id = tags.user_id
AND id = profile.user_id
AND id = photos.user_id
ORDER BY id
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36520912
which cupid_tag do you want to display?  You will need to place that into your logic.  Maybe have a group by function (i.e. max, min).  Alternatively, if you don't need to see the actual matching cupid_tag, then remove the column from the SELECT clause and put a DISTINCT keyword to it.
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 36520918
SELECT
u.id AS id,
u.sess_id AS sess_id,  
u.username AS username,
u.session_last_active AS last_active,
u.email AS email,
u.contact AS contact,
profile.countryLive AS country,
profile.stateLive  AS state,
profile.cityLive AS city,
profile.updated AS profile_upd,
profile.birthdate_day AS birthdate_day,
profile.birthdate_month AS birthdate_month,
profile.birthdate_year AS birthdate_year,
max(tags.cupid_tag) AS cupid_tag,
profile.gender AS gender,
profile.updated AS profile_upd,
photos.image1 AS image1,
photos.updated AS photos_upd
FROM users AS u
INNER JOIN user_upd_cupid_tags AS tags, user_upd_my_profile AS profile, user_upd_photos AS photos
WHERE tags.cupid_tag IN (2000,2012,2024,2027,2039)
AND id = tags.user_id
AND id = profile.user_id
AND id = photos.user_id
GROUP BY u.id, u.sess_id /*put all the other columns in the select clause here*/
ORDER BY id

OR, as I said earlier, remove cupid_tag from the SELECT clause and use DISTINCT.  Same logic if you have more than one profile/photo for each user.

0
 

Author Closing Comment

by:sabecs
ID: 36520937
Thanks  johanntagle, thats works great.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Transferring database from one server to another 1 63
Need help with a query 6 67
Best way to find a random number in a list of a million numbers 11 44
join tables 4 50
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

16 Experts available now in Live!

Get 1:1 Help Now