Solved

MySQL - ned to join multiple tables

Posted on 2011-09-11
13
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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
 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 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