Having problems with a one to many relationship

Transact-SQL
1

Evening all,

I've been trying to get this to work for the last few hours with little success. I'm trying to get some data out of an old database. Basically I have a table that has a ClientID and address details and another table with ClientID and contact details. Unfortunately the contact details have a key column not separate columns for each type of contact... OK So the query I have at the moment is:

Select 
Name, Notes,
KnownAs, AddressLine1, AddressLine2, Town, City, County, PostCode,
Salutation, FirstName, LastName, Position, 
  
(Select ContactNumber Where ContactNumberTypeID = 'SYSEMAIL') As Email,
(Select ContactNumber Where ContactNumberTypeID = 'SYSFAX') As Fax,
(Select ContactNumber Where ContactNumberTypeID = 'SYSTEL') As Tel,
(Select ContactNumber Where ContactNumberTypeID = 'SYSMOBILE') As Mob,
(Select ContactNumber Where ContactNumberTypeID = 'SYSUNKNOWN') As Unknown

From [dbo].[ptdClient]

Inner Join [dbo].[ptdClientContactNumber]
On ptdClient.ClientID = ptdClientContactNumber.ClientID

Where Name = 'My_Test_Record'

Essentially the works fine, but it outputs 4 lines (1 for each ContactNumberTypeID). I only want 1 line out put but with the 4 ContactNumberTypeID's in separate columns. I've tried grouping but I can't get it to work... Can anyone offer any pointers please...

Thanks

Dave

PS Hope it makes sense...

2 
    Select 
Name, Notes,
KnownAs, AddressLine1, AddressLine2, Town, City, County, PostCode,
Salutation, FirstName, LastName, Position, 
  
(Select ContactNumber   FROM [dbo].[ptdClientContactNumber]  AS A
            Where ptdClient.ClientID = A.ClientID 
            AND ContactNumberTypeID = 'SYSEMAIL') As Email,

(Select ContactNumber   FROM [dbo].[ptdClientContactNumber]  AS B
            Where ptdClient.ClientID = B.ClientID 
            AND  ContactNumberTypeID = 'SYSFAX') As Fax,

(Select ContactNumber   FROM [dbo].[ptdClientContactNumber]  AS C
            Where ptdClient.ClientID = C.ClientID 
            AND   ContactNumberTypeID = 'SYSTEL') As Tel,

(Select ContactNumber   FROM [dbo].[ptdClientContactNumber]  AS D
            Where ptdClient.ClientID = D.ClientID 
            AND   ContactNumberTypeID = 'SYSMOBILE') As Mob,

(Select ContactNumber   FROM [dbo].[ptdClientContactNumber]  AS E
            Where ptdClient.ClientID = E.ClientID 
            AND   ContactNumberTypeID = 'SYSUNKNOWN') As Unknown

From [dbo].[ptdClient] AS ptdClient

--Inner Join [dbo].[ptdClientContactNumber]
--On ptdClient.ClientID = ptdClientContactNumber.ClientID

Where Name = 'My_Test_Record'
3 
Select 
c.Name, c.Notes,
c.KnownAs, c.AddressLine1, c.AddressLine2, c.Town, c.City, c.County, c.PostCode,
c.Salutation, c.FirstName, c.LastName, c.Position,
oa_cn.Email, oa_cn.Fax, oa_cn.Tel, oa_cn.Mob, oa_cn.Unknown 

From [dbo].[ptdClient] c
Outer Apply (
    Select
    Max(Case When ContactNumberTypeID = 'SYSEMAIL' Then ContactNumber End) As Email,
    Max(Case When ContactNumberTypeID = 'SYSFAX' Then ContactNumber End) As Fax,
    Max(Case When ContactNumberTypeID = 'SYSTEL' Then ContactNumber End) As Tel,
    Max(Case When ContactNumberTypeID = 'SYSMOBILE' Then ContactNumber End) As Mob,
    Max(Case When ContactNumberTypeID = 'SYSUNKNOWN' Then ContactNumber End) As Unknown
    From [ptdClientContactNumber] cn
    Where cn.ClientID = c.ClientID    
) As oa_cn

Where c.Name = 'My_Test_Record'
4

You can use pivot to do the desired output

declare @ptdClient AS TABLE
    (    ClientID INT NOT NULL,
        Name VARCHAR(50) NOT NULL,
        Notes VARCHAR(50),
        AddressLine1 VARCHAR(50))

INSERT INTO @ptdClient(ClientID,Name,Notes,AddressLine1)
VALUES(1,'My_Test_Record','Notes 1','Address 1'),
    (2,'Test2','Notes 2','Address 2')

declare @ptdClientContactNumber AS TABLE
(   C_ID INT IDENTITY(1,1) NOT NULL,
    ClientID INT NOT NULL,
    ContactNumber INT NOT NULL,
    ContactNumberTypeID VARCHAR(50) NOT NULL)
INSERT INTO @ptdClientContactNumber(ClientID,ContactNumber,ContactNumberTypeID)
VALUES(1,11111,'SYSEMAIL'),
        (1,2222,'SYSFAX'),
        (1,3333,'SYSTEL'),
        (1,4444,'SYSMOBILE'),
        (1,5555,'SYSUNKNOWN'),
        (2,6677,'SYSEMAIL')





;WITH aCTE
AS(SELECT Name,Notes,AddressLine1,
        ContactNumber,ContactNumberTypeID
    FROM @ptdClient AS A
    INNER JOIN @ptdClientContactNumber AS B
    ON A.ClientID = B.ClientID)

Select 
 Name
,Notes
,AddressLine1
,C.SYSEMAIL As Email
,C.SYSFAX AS Fax
,C.SYSTEL AS Tel
,C.SYSMOBILE AS Mob
,C.SYSUNKNOWN AS Unknown
From aCTE
PIVOT
    (
        MAX(ContactNumber) FOR ContactNumberTypeID IN ([SYSEMAIL],[SYSFAX],[SYSTEL],[SYSMOBILE],[SYSUNKNOWN])
    )C

Where Name = 'My_Test_Record'



Name    Notes    AddressLine1    Email    Fax    Tel    Mob    Unknown
My_Test_Record    Notes 1    Address 1    11111    2222    3333    4444    5555
5

Thanks guys,

I've implemented Stepsons first reply and it works perfectly for single outputs but fails with multiple outputs.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Would the other 2 solutions be better for this scenario?

Thanks

Dave

Oh and I've added a few more joins as I was missing some data...

6

well the subqueries in that solution need to be qualified to return just one result. Scott's solution looks good to me

7

Yeah, I'm tired and probably missing something simple
This keeps giving me the error 'The multi-part identifier could not be bound.' on the Inner Joins at the bottom...

Select
ltdClientStatus.Description,
gtdBranch.Description, 
Name, ptdClient.Notes,
KnownAs, AddressLine1, AddressLine2, Town, City, County, PostCode,
Salutation, Replace(TitleID, 'SYS', '') As Title, FirstName, LastName, Position, 
oa_cn.Email, oa_cn.Fax, oa_cn.Tel, oa_cn.Mob, oa_cn.Unknown, oa_cn.URL

From [dbo].[ptdClient] c
Outer Apply (
    Select
    Max(Case When ContactNumberTypeID = 'SYSEMAIL' Then ContactNumber End) As Email,
    Max(Case When ContactNumberTypeID = 'SYSFAX' Then ContactNumber End) As Fax,
    Max(Case When ContactNumberTypeID = 'SYSTEL' Then ContactNumber End) As Tel,
    Max(Case When ContactNumberTypeID = 'SYSMOBILE' Then ContactNumber End) As Mob,
    Max(Case When ContactNumberTypeID = 'SYSUNKNOWN' Then ContactNumber End) As Unknown,
    Max(Case When ContactNumberTypeID = 'SYSURL' Then ContactNumber End) As URL
    From [dbo].[ptdClientContactNumber] cn
    Where cn.ClientID = c.ClientID    
) As oa_cn

Inner Join [dbo].[ptdClientAddress]
On ptdClient.ClientID = ptdClientAddress.ClientID

Inner Join [dbo].[ptdClientContact]
On ptdClient.ClientID = ptdClientContact.ClientID

Inner Join [dbo].[ltdClientStatus]
On ptdClient.ClientStatusID = ltdClientStatus.ClientStatusID

Inner Join [Teach Education Ltd].[dbo].[gtdBranch]
On ptdClient.BranchID = gtdBranch.BranchID

Many thanks

Dave

8

OIC, either: remove the alias "c" from ptdClient. or change the join predicates to c.whatever = other_column

9

Yeah that might have helped, my bad. I'll copy the entire error message...

Msg 4104, Level 16, State 1, Line 22
The multi-part identifier "ptdClient.ClientID" could not be bound.
Msg 4104, Level 16, State 1, Line 25
The multi-part identifier "ptdClient.ClientID" could not be bound.
Msg 4104, Level 16, State 1, Line 28
The multi-part identifier "ptdClient.ClientStatusID" could not be bound.
Msg 4104, Level 16, State 1, Line 31
The multi-part identifier "ptdClient.BranchID" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "ptdClient.Notes" could not be bound.

10

OIC, either: remove the alias "c" from ptdClient. or change the join predicates to c.whatever = other_column

11

For the first solution, you can add :

Select TOP (1) ContactNumber   FROM

Or

Select MAX(ContactNumber) FROM

PS:Always is better to add some sample data , so that we can test our solutions.

12

Yes that's a fair point, was tired and grumpy last night and probably not thinking straight!
Big thank you to you all the query is now working as it should and outputting all the data I need :grinning:

Problem now is I can't seem to output it to an excel file - but I'll start a new thread for that....

Many thanks

Dave

13

Where is the DDL? Why is the little code you did post so wrong? Please read any book on basic data modeling. There is no such thing as universal, generic “name”, “town”, etc. The ISO-11179 rules require _ syntax. Likewise, you can have a “_type” or a “_id”, but never that absurd hybrid. What is your “blood_type_id”? See how silly it is. But even worse, it implied in the world of bad T-SQL that you used an IDENTITY table property (it is not a column) and threw out RDBMS in favor of dialect and sequential file designs.

You have some kludges that depend on assumptions about the columns and the tables. Since you failed to post DDL (minimal Netiquette for the past 30+ years), we cannot know. For example, my first guess was that “name” was supposed to be “client_name”, then we find “last_name” and “first_name” later. I am old, so I do not feel like guessing why a “town_something” and “city_something” are totally different attributes instead of types of geographic units and how a place with one postal code has both of whatever these attributes are (Name? Population?).

Your narrative is for a report that converts the values of the contact_type into attributes. This is not RDBMS and should be done in a presentation layer.

Want to try again, follow the forum rules and do it right?