Return all rows from first table & specific values from joined table?

Transact-SQL
1

Afternoon all,

I'm trying to output all data from a table and specific data from a joined table, I seem to be struggeling and can't get my head around it.

CREATE TABLE [dbo].[Customers](
	[C_ID] [int] NOT NULL,
	[C_Name] [nvarchar](20) NOT NULL,
        [C_Status] [nvarchar](20) NOT NULL,
        [C_Type] [nvarchar](20) NOT NULL,
	);
	
INSERT INTO Customers(C_ID, C_Name, C_Status, C_Type)
	VALUES
	('1000', 'John', 'Live', 'Telephone'),
	('1001', 'Jane', 'Live', 'In Person'),
	('1002', 'Bob', 'Live', 'Email')
        ('1003', 'Emma', 'Live', 'Catalogue')
    ;

CREATE TABLE [dbo].[Customer_Contact](
	[CC_ID] [int] NOT NULL,
    [CC_CustID] [int] NOT NULL,
	[CC_Notes] [nvarchar](20) NOT NULL
	);
	
INSERT INTO Customer_Contact(CC_CustID, CC_Notes)
	VALUES
	('1000', 'Phone call to discuss blar'),
	('1000', 'Emailed customer to advise them blar'),
	('1000', 'Sent SMS to warn item will be late'),
	('1001', 'Phone call to chase for details'),
	('1001', 'Email to advise to price change'),
	('1002', 'Phone call to place order')
    ;

The Query I currently have, which doesn't out put the correct data is:

SELECT C_Name, C_Status, C_Type, CC_Notes
FROM Customers
JOIN Customer_Contact ON C_ID = CC_CustID
WHERE CC_Notes LIKE '%Phone%'

I would like to get something like:
C_Name, C_Status, C_Type, CC_Notes
John, Live, Telephone, Phone call to discuss blar
Jane, Live, In Person, Phone call to chase for details
Bob, Live, Email, Phone call to place order
Emma, Live, Catalogue, Null

It would be really nice to go one step further, but not sure its possible, and output 2 different notes on the same line to get something like this:

C_Name, C_Status, C_Type, CC_Notes_Phone, CC_Notes_Email
John, Live, Telephone, Phone call to discuss blar, Emailed customer to advise them blar
Jane, Live, In Person, Phone call to chase for details, Email to advise to price change
Bob, Live, Email, Phone call to place order, Null
Emma, Live, Catalogue, Null, Null

Hope that makes sense...
Many thanks

Dave

2 

SELECT C_Name, C_Status, C_Type, CC_Notes_Phone, CC_Notes_Email
FROM Customers C
OUTER APPLY (
    SELECT 
        CC_Phone.CC_Notes AS CC_Notes_Phone,
        CC_Email.CC_Notes AS CC_Notes_Email
    FROM (
        SELECT TOP (1) CC_Notes
        FROM dbo.Customer_Contact CC
        WHERE CC.CC_CustID = C.C_ID AND CC.CC_Notes LIKE 'Phone%'
        ORDER BY CC_ID
    ) AS CC_Phone
    OUTER APPLY (
        SELECT TOP (1) CC_Notes
        FROM dbo.Customer_Contact CC
        WHERE CC.CC_CustID = C.C_ID AND CC.CC_Notes NOT LIKE 'Phone%'
        ORDER BY CC_ID
    ) AS CC_Email
) AS CC
1 Like
3

Wow, thats awesome, works perfectly. I was even able to modify it, slightly :smiley:
Not sure i'd have come up with that by myself, thank you!!

Every time you guys come up with these great queries it makes me realise how much more i have to learn!!

4

hi

hope this helps

This is a simple short statement

create data script

DROP TABLE [dbo].[Customers]
CREATE TABLE [dbo].[Customers]( [C_ID] [int] NOT NULL, [C_Name] nvarchar NOT NULL, [C_Status] nvarchar NOT NULL, [C_Type] nvarchar NOT NULL );

INSERT INTO Customers(C_ID, C_Name, C_Status, C_Type)
VALUES
('1000', 'John', 'Live', 'Telephone')
,('1001', 'Jane', 'Live', 'In Person')
,('1002', 'Bob', 'Live', 'Email')
,('1003', 'Emma', 'Live', 'Catalogue')

DROP TABLE [dbo].[Customer_Contact]
CREATE TABLE [dbo].[Customer_Contact]( [CC_CustID] [int] NOT NULL, [CC_Notes] nvarchar NOT NULL );

INSERT INTO Customer_Contact(CC_CustID, CC_Notes)
VALUES
('1000', 'Phone call to discuss blar')
,('1000', 'Emailed customer to advise them blar')
,('1000', 'Sent SMS to warn item will be late')
,('1001', 'Phone call to chase for details')
,('1001', 'Email to advise to price change')
,('1002', 'Phone call to place order')

DROP TABLE [dbo].[Output]
CREATE TABLE [dbo].[Output](C_Name nvarchar NOT NULL, C_Status nvarchar NOT NULL, C_Type nvarchar NOT NULL, CC_Notes nvarchar NULL)
INSERT INTO [dbo].[Output](C_Name , C_Status ,C_Type , CC_Notes )
VALUES
('John', 'Live', 'Telephone', 'Phone call to discuss blar')
,('Jane', 'Live', 'In Person', 'Phone call to chase for details')
,('Bob', 'Live', 'Email', 'Phone call to place order')
,('Emma', 'Live', 'Catalogue', Null)

SELECT 
   C_Name
 , C_Status
 , C_Type
 , CC_Notes
FROM 
   Customers a
     LEFT JOIN 
  ( SELECT * FROM Customer_Contact WHERE CC_Notes like '%Phone%' ) b
        ON a.C_ID = b.CC_CustID

image
image892×752 17.5 KB