Detailed SELECT values

Databases
1

I’m not sure what you would call this, but take for example the following tables:

ContactTable
ContactId, AddressId, AddressType

AddressTable
AddressId, Street, City, County, Postcode

There was multiple AddressType’s; such as Business, Home. How can I do a select to retrieve each in the one statement?

SELECT Street as BusinessStreet (when AddressType=Business)
Street as HomeStreet (when AddressType=Home)
 FROM ContactTable 
JOIN AddressTable on ContactTable.AddressId=AddressTable.AddressId

Thanks.

2

I guess you want the business and home street for each contact id?


SELECT 
    ContactTable.ContactId
  , MAX(CASE WHEN ContactTable.AddressType = 'Business' THEN AddressTable.Street
             ELSE ''
        END) as BusinessStreet 
  , MAX(CASE WHEN ContactTable.AddressType = 'Home' THEN AddressTable.Street
             ELSE ''
        END) as HomeStreet 
FROM ContactTable 
INNER JOIN AddressTable 
ON ContactTable.AddressId = AddressTable.AddressId
GROUP BY ContactTable.ContactId
3

Thank you. I attempted to run a test for one particular contact (leaving out the group by clause) like so:

SELECT
ContactTable.ContactId
, MAX(CASE WHEN ContactTable.AddressType = 'Business' THEN AddressTable.Street
 ELSE ''
END) as BusinessStreet 
, MAX(CASE WHEN ContactTable.AddressType = 'Home' THEN AddressTable.Street
 ELSE ''
END) as HomeStreet
FROM ContactTable
INNER JOIN AddressTable  ON ContactTable.AddressId = AddressTable.AddressId[B]
WHERE ContactTable.ContactId=1[/B]

It then returned the following error:

SQL0122N  A SELECT statement with no GROUP BY clause contains a column name
and a column function in the SELECT clause, or a column name is contained in
the SELECT clause but not in the GROUP BY clause.  SQLSTATE=42803

Would it be safe to assume the group-by is required because of the case declarations? Didn’t think the group-by was needed because I used the where clause to specify a single contact.

4

Sorry, got it going now. No doubt I will have more questions, so can only apologise in advance! :slight_smile:

5

GROUP BY is needed because in the SELECT you have MIN() and MAX().

Don’t worry, ask! :slight_smile:

6

if you were actually using mysql—the forum you posted in—it woulda worked (mysql is pretty liberal about interpreting standard sql)

i’ve flagged the thread to have it moved to the Databases forum

7

Ah, so say if I had another table relationship. For example:

MainContactTable #this links from person table
MainContactId, PersonId

ContactAddressTable
ContactAddressId, MainContactId, AddressId, AddressType

AddressTable
AddressId, Street, City, County, Postcode

EmailTable
EmailId, MainContactId, EmailAddress

The query you gave previously works perfect, but when adding another table relationship (EmailTable), it complains about the GROUP BY. I guess I’m just trying to learn what it requires (knowing what to add inside the GROUP BY is a tricky part for me).

Here is the query used (I’ve bolded the part which I’ve added, which recieves the SQL0122N error). Only when adding that new relationship it doesn’t like…


SELECT MainContactTable.MainContactId,
    MAX(CASE WHEN ContactAddressTable.AddressType='BUSINESS' THEN AddressTable.Street
        ELSE ''
        END) as "Provider street",
    MAX(CASE WHEN ContactAddressTable.AddressType='BUSINESS' THEN AddressTable.City
        ELSE ''
        END) as "Provider city",
    MAX(CASE WHEN ContactAddressTable.AddressType='BUSINESS' THEN AddressTable.County
        ELSE ''
        END) as "Provider county",
    MAX(CASE WHEN ContactAddressTable.AddressType='BUSINESS' THEN AddressTable.Postcode
        ELSE ''
        END) as "Provider postcode",
    MAX(CASE WHEN ContactAddressTable.AddressType='MAIL' THEN AddressTable.Street
        ELSE ''
        END) as "Personal street",
    MAX(CASE WHEN ContactAddressTable.AddressType='MAIL' THEN AddressTable.City
        ELSE ''
        END) as "Personal city",
    MAX(CASE WHEN ContactAddressTable.AddressType='MAIL' THEN AddressTable.County
        ELSE ''
        END) as "Personal county",
    MAX(CASE WHEN ContactAddressTable.AddressType='MAIL' THEN AddressTable.Postcode
        ELSE '' 
        END) as "Personal postcode",
[B][color=red][/B][B]EmailTable[/B][B][COLOR=red].EmailAddress as "E-mail Address"[/COLOR][/B][/color]
FROM MainContactTable
INNER JOIN ContactAddressTable on MainContactTable.MainContactId=ContactAddressTable.MainContactId
INNER JOIN AddressTable on ContactAddressTable.AddressId=AddressTable.AddressId
[B][color=red]INNER JOIN EmailTable on ContactAddressTable.MainContactId=EmailTable.MainContactId[/B][/color]
WHERE MainContactTable.PersonId=1
GROUP BY MainContactTable.MainContactId
8

Like r937 said, mysql is very relaxed, but other databases want you to specify all columns from the SELECT clause that are not the result of GROUP BY functions (like MIN, MAX, ecc).
So I guess that you’ll have to add EmailTable.EmailAddress to the GROUP BY clause.

9

That makes sense.

My final question would be, if there was no email row, will the query not pick up anything for that contact, or will it leave a blank value for the EmailAddress field? I’d prefer to just leave a blank value… so I’m guessing that is some specific JOIN in order to do that? LEFT OUTER possibly?

10

you’re a quick learner :smiley: