Solved

MySQL - ned to join multiple tables

Posted on 2011-09-11
13
298 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

13 Experts available now in Live!

Get 1:1 Help Now