Solved

MySQL - ned to join multiple tables

Posted on 2011-09-11
13
306 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

696 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