CODEWITHSUNDEEP
mysqlsqlselectsubquery
Alex
Alex

Reputation: 3

SQL Select with Conditional Fields (Subquerys maybe?)

I'm trying to set up an SQL View that returns all details of a contact. Name and Last name are saved in a Table "Person", the contact info is saved in "contact" and the type of contact info (email, phone 1, phone2) is saved in "contact_types".

I want to return all the information in 1 row but I can't really figure it out. So far my best result is with:

SELECT 

Person.ID, Person.Title, Person.Firstname, Person.Lastname, 
    ( SELECT MAX(ContactInfo.InfoText) FROM ContactInfo WHERE ContactInfo.ContactTypID = '1' AND ContactInfo.PersonID = Person.ID  ) AS Phone_Business,
    ( SELECT MAX(ContactInfo.InfoText) FROM ContactInfo WHERE ContactInfo.ContactTypID = '2' AND ContactInfo.PersonID = Person.ID  ) AS Phone_Private,
    ( SELECT MAX(ContactInfo.InfoText) FROM ContactInfo WHERE ContactInfo.ContactTypID = '3' AND ContactInfo.PersonID = Person.ID  ) AS Phone_Mobile,
    ( SELECT MAX(ContactInfo.InfoText) FROM ContactInfo WHERE ContactInfo.ContactTypID = '5' AND ContactInfo.PersonID = Person.ID  ) AS Email
        
    FROM Person

This statement results in duplicate outputs - 4 identical rows, even with MAX(). It is apparently one row per subquery. How can I only receive 1 row per ID?

I'm quite new to SQL, any suggestions would be helpful!

Edit: Sample Data:

Table Person:

ID Title Firstname Lastname
1 Mr. Tom Selleck
2 Mr. Fred Miller

Table ContactInfo

PersonID InfoText ContactTypeID
1 [email protected] 5
2 +1 12345 678 1
1 +1 98765 432 2

Table ContactTypeID

ID InfoText
1 phone_business
2 phone_private
5 email

Expected Result:

ID Title Firstname Lastname Phone_Business Phone_Private Phone_Mobile Email
1 Mr. Tom Selleck NULL +1 98765 432 NULL [email protected]
2 Mr. Fred Miller +1 12345 678 NULL NULL NULL

It works so far, but I'd get each row 4 times.

Upvotes: 0

Views: 71

Answers (1)

FanoFN
FanoFN

Reputation: 7114

You can do it like this:

SELECT P.ID, P.Title, P.Firstname, P.Lastname,
       MAX(CASE WHEN C.ContactTypeID = '1' THEN C.InfoText END) AS Phone_Business,
       MAX(CASE WHEN C.ContactTypeID = '2' THEN C.InfoText END) AS Phone_Private,
       MAX(CASE WHEN C.ContactTypeID = '3' THEN C.InfoText END) AS Phone_Mobile,
       MAX(CASE WHEN C.ContactTypeID = '5' THEN C.InfoText END) AS Email
  FROM Person P 
LEFT JOIN ContactInfo C
 ON P.ID=C.PersonID
GROUP BY P.ID, P.Title, P.Firstname, P.Lastname;

Just a single LEFT JOIN between Person table and ContactInfo. The Person table here acts as a reference table. Then use MAX() with CASE expression (also possible with GROUP_CONCAT()) in SELECT.

Here's a demo fiddle

Upvotes: 2

Related Questions