MySQL - ned to join multiple tables

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.
sabecsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johanntagleCommented:
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
sabecsAuthor Commented:
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
johanntagleCommented:
Post your trial SQL statements here and let's see what's wrong with them.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

sabecsAuthor Commented:
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
sabecsAuthor Commented:
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
johanntagleCommented:
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
johanntagleCommented:
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
Pratima PharandeCommented:
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
sabecsAuthor Commented:
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
sabecsAuthor Commented:
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
johanntagleCommented:
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
johanntagleCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sabecsAuthor Commented:
Thanks  johanntagle, thats works great.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.