Hi I am trying to create a view from two tables.
Table 1 Sales
Cust_ID | Name | Genre | Sales Person | Last Order |
-
A123 | John | Fiction | Bill | 543A |
A123 | John | Sci-Fi | Bill | 534G |
B432 | Mark | Music | Ted | 748H |
C991 | Kevin | Sci-Fi | Bob | 017S |
C991 | Kevin | Classics | Bob | 663H |
C991 | Kevin | Fiction | Bob | 882G |
D912 | Syd | Music | Ted | 917F |
G941 | Paul | Sci-Fi | Bill | 991C |
G941 | Paul | Music | Bill | 947D |
Each customer will only have one record for each Genre.
Table 2 Acc_holders
Cust_ID | Name | Account No | Balance |
-
A123 | John | ABT110234 | 12.34 |
B432 | Mark | ADE145521 | 53.32 |
C991 | Kevin | NDU11E234 | 55.90 |
F723 | Andy | GGE124349 | 22.60 |
H882 | Sammy | NJW310264 | 12.99 |
I731 | Jane | HAT219845 | 55.23 |
cUST_ID is unique in this table.
A customer may be in either one or both tables
I am looking to create a view that will contain the following
Cust ID | Name | Has Account | Balance | Sci-Fi | Fiction | Music | Classics |
A123 | John | Y | 12.34 | Y | Y | N | N |
B432 | Mark | Y | 53.32 | N | N | Y | N |
C991 | Kevin | Y | 55.90 | Y | Y | N | N |
D912 | Syd | N | NULL | Y | Y | N | Y |
F723 | Andy | Y | 22.60 | N | N | Y | N |
G941 | Paul | N | NULL | N | N | N | N |
H882 | Sammy | Y | 12.99 | N | N | N | N |
I731 | Jane | Y | 55.23 | N | N | N | N |
Ok so I am trying to figure out how I can create a summary view that contains all my customers from both tables.
I need a single record for each customer and for it to show a balance and if they have a account from tabel 2 and if there are any genres from table 1
so far I have
SELECT DISTINCT
TOP (100) PERCENT dbo.SALES.CUST_ID,
dbo.SALES.Name
FROM dbo.SALES FULL OUTER JOIN
dbo.Acc_holders ON dbo.SALES.CUST_ID, = dbo.Acc_holders.CUST_ID, AND dbo.SALES.Name = dbo.Acc_holders.Name
ORDER BY dbo.SALES.CUST_ID
This gives me the first two columns but I can't figure out how to do the rest.
Any help would be very much appreciated.
Cheers.
The example below returns the results that I think you are expecting (in accordance with your source data).
Incidentally, if you are intending to use the code inside a View then it is not recommended to include an ORDER BY clause within the View, as you displayed in your example. If ordering of the results is required then you should use ORDER BY when SELECTing from the View instead.
Chris
DECLARE @.Sales TABLE
(
Cust_ID CHAR(4) NOT NULL,
[Name] VARCHAR(100) NOT NULL,
[Genre] VARCHAR(20) NOT NULL,
[Sales Person] VARCHAR(20),
[Last Order] CHAR(4) NOT NULL
)
DECLARE @.Acc_holders TABLE
(
Cust_ID CHAR(4) NOT NULL PRIMARY KEY,
[Name] VARCHAR(100) NOT NULL,
[Account No] CHAR(9) NOT NULL,
[Balance] MONEY NOT NULL
)
INSERT INTO @.Sales
SELECT 'A123','John','Fiction','Bill','543A' UNION
SELECT 'A123','John','Sci-Fi','Bill','534G' UNION
SELECT 'B432','Mark','Music','Ted','748H' UNION
SELECT 'C991','Kevin','Sci-Fi','Bob','017S' UNION
SELECT 'C991','Kevin','Classics','Bob','663H' UNION
SELECT 'C991','Kevin','Fiction','Bob','882G' UNION
SELECT 'D912','Syd','Music','Ted','917F' UNION
SELECT 'G941','Paul','Sci-Fi','Bill','991C' UNION
SELECT 'G941','Paul','Music','Bill','947D'
INSERT INTO @.Acc_holders
SELECT 'A123','John','ABT110234',12.34 UNION
SELECT 'B432','Mark','ADE145521',53.32 UNION
SELECT 'C991','Kevin','NDU11E234',55.9 UNION
SELECT 'F723','Andy','GGE124349',22.6 UNION
SELECT 'H882','Sammy','NJW310264',12.99 UNION
SELECT 'I731','Jane','HAT219845',55.23
SELECT t.Cust_ID,
t.[Name],
CASE WHEN EXISTS (SELECT 1 FROM @.Acc_holders a WHERE a.Cust_ID = t.Cust_ID) THEN 'Y' ELSE 'N' END AS [Has Account],
ac.Balance,
CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Sci-Fi') THEN 'Y' ELSE 'N' END AS [Sci-Fi],
CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Fiction') THEN 'Y' ELSE 'N' END AS [Fiction],
CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Music') THEN 'Y' ELSE 'N' END AS [Music],
CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Classics') THEN 'Y' ELSE 'N' END AS [Classics]
FROM
|||(SELECT Cust_ID, [Name]
FROM @.Sales
UNION
SELECT Cust_ID, [Name]
FROM @.Acc_holders) t
LEFT JOIN @.Acc_Holders ac ON ac.Cust_ID = t.Cust_ID
That worked a treat.
Thank you very much!
Kevin.
|||This should be fasterselect
Cust_ID = coalesce(s.Cust_ID, a.Cust_ID),
[Name] = coalesce(s.[Name], a.[Name]),
[Has Account] = case when a.Cust_ID is null then 'N' else 'Y' end,
a.Balance,
[Sci-Fi] = coalesce(s.[Sci-Fi], 'N'),
[Fiction] = coalesce(s.[Fiction], 'N'),
[Music] = coalesce(s.[Music], 'N'),
[Classics] = coalesce(s.[Classics], 'N')
from
(
select Cust_ID, [Name],
[Sci-Fi] = max(case when Genre = 'Sci-Fi' then 'Y' else 'N' end),
[Fiction] = max(case when Genre = 'Fiction' then 'Y' else 'N' end),
[Music] = max(case when Genre = 'Music' then 'Y' else 'N' end),
[Classics] = max(case when Genre = 'Classics' then 'Y' else 'N' end)
from @.Sales
group by Cust_ID, [Name]
) s full outer join @.Acc_Holders a
on s.Cust_ID = a.Cust_ID|||
Thanks,
I have it working for now but when it comes to optimising the code I will give it a go.
Cheers,
Kevin.
