Complex MySQL query with multiple select statements

Question

I have three tables in Mysql that are link together:

Profile (ID, Name, Stuff..)

Contact(ID, ProfileID,desc,Ord)

Address(ID,ProfileID, desc, Ord)

Now I need to select all profile from the profile table, with the “desc” field from Contact and Address where Ord = 1. (this is for a search function where in a table I’ll display the name, main contact info and main Address of a client.

I can currently do this with three separate SQL request:

SELECT Name, ID FROM Profile WHERE name=”bla”

Then in a foreach loop, I’ll run the other two requests:

SELECT ProfileID, desc FROM Contact WHERE ProfileID=MyProfileID AND Ord=1
SELECT ProfileID, desc FROM Address WHERE ProfileID=MyProfileID AND Ord=1

I know you can do multiple SELECT in one query, is there a way I could group all three SELECT into one query?

Taryn · Accepted Answer · 2013-03-01T15:28:14.490

You should be able to JOIN the tables on the profile.id and the profileid in the other tables.

If you are sure the profileid exists in all three tables, then you can use an INNER JOIN. The INNER JOIN returns matching rows in all of the tables:

select p.id,
  p.name,
  c.desc ContactDesc,
  a.desc AddressDesc
from profile p
inner join contact c
  on p.id = c.profileid
inner join address a
  on p.id = a.profileid
where p.name = 'bla'
  and c.ord = 1
  and a.ord = 1

If you are not sure that you will have matching rows, then you can use a LEFT JOIN:

select p.id,
  p.name,
  c.desc ContactDesc,
  a.desc AddressDesc
from profile p
left join contact c
  on p.id = c.profileid
  and c.ord = 1
left join address a
  on p.id = a.profileid
  and a.ord = 1
where p.name = 'bla'

If you need help learning JOIN syntax, here is a great visual explanation of joins

Thank you so much! 1 for the explanation and 2nd for the link, both your info and JW info help me get a better understanding of SQL... (I love understanding and learning the answer, not just copy paste!!!! — Daniel Berthiaume, Mar 02 '13 at 00:21

score 1 · Answer 2 · answered Mar 01 '13 at 15:23

This query below only selects column when an ID from Profile table has atleast one match on tables: Contact and Address. If one or both of them are nullable, use LEFT JOIN instead of INNER JOIN because LEFT JOIN displays all records from the Left-hand side table regardless if it has a match on other tables or not.

SELECT  a.*, 
        b.desc as BDESC,
        c.desc as CDESC
FROM    Profile a
        INNER JOIN Contact b
            ON a.ID = b.ProfileID
        INNER JOIN Address c
            ON a.ID = c.ProfileID
WHERE   b.ORD = 1 AND
        c.ORD = 1 AND
        a.Name = 'nameHERE'

The LEFT JOIN version:

SELECT  a.*, 
        b.desc as BDESC,
        c.desc as CDESC
FROM    Profile a
        INNER JOIN Contact b
            ON a.ID = b.ProfileID AND b.ORD = 1
        INNER JOIN Address c
            ON a.ID = c.ProfileID AND c.ORD = 1
WHERE   a.Name = 'nameHERE'

To further gain more knowledge about joins, kindly visit the link below:

Visual Representation of SQL Joins

HI JW, you can take a look at the comment left for bluefeet, the thank you also appliedto you!!! — Daniel Berthiaume, Mar 02 '13 at 00:22

score 0 · Answer 3 · answered Mar 01 '13 at 19:12

i have created working demo as your requirement :

The query bellow will retrieve all matching records from the database.its retrieving profile id,name stufff and description of contact tables

select p.id,p.name,p.stauff,c.descr,a.descr from profile as p
 inner join contact as c on c.profileid=p.id
 inner join address as a on a.profileid=p.id
 where p.name="bla" and c.ord=1 and a.ord=1

Complex MySQL query with multiple select statements

3 Answers3