A view from two tables

[Elroacho]

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.

 

[bborissov]

Something like this:

DECLARE @Test1 TABLE (Cust_Id char(4), Name varchar(50), Genre varchar(50), Sales_Person varchar(50), Last_Order char(4))
INSERT INTO @Test1 VALUES ('A123','John','Fiction','Bill','543A')
INSERT INTO @Test1 VALUES ('A123','John','Sci-Fi','Bill','534G')
INSERT INTO @Test1 VALUES ('B432','Mark','Music','Ted','748H')
INSERT INTO @Test1 VALUES ('C991','Kevin','Sci-Fi','Bob','017S')
INSERT INTO @Test1 VALUES ('C991','Kevin','Classics','Bob','663H')
INSERT INTO @Test1 VALUES ('C991','Kevin','Fiction','Bob','882G')
INSERT INTO @Test1 VALUES ('D912','Syd','Music','Ted','917F')
INSERT INTO @Test1 VALUES ('G941','Paul','Sci-Fi','Bill','991C')
INSERT INTO @Test1 VALUES ('G941','Paul','Music','Bill','947D')


DECLARE @Test2 TABLE (Cust_Id char(4), Name varchar(50), AccNo char(9), Balance Numeric(10,2))
INSERT INTO @Test2 VALUES ('A123','John','ABT110234',12.34)
INSERT INTO @Test2 VALUES ('B432','Mark','ADE145521',53.32)
INSERT INTO @Test2 VALUES ('C991','Kevin','NDU11E234',55.90)
INSERT INTO @Test2 VALUES ('F723','Andy','GGE124349',22.60)
INSERT INTO @Test2 VALUES ('H882','Sammy','NJW310264',12.99)
INSERT INTO @Test2 VALUES ('I731','Jane','HAT219845',55.23)


SELECT Test1.Cust_Id,
       Test1.Name,
       CASE WHEN Test2.Cust_Id IS NULL 
            THEN 'N'
            ELSE 'Y' END Has_Account,
       SUM(Test2.Balance) AS Balance,
       [Sci-Fi],
       Fiction,
       Music,
       Classics
FROM (SELECT Cust_Id,
             Name,
       CASE WHEN SUM(CASE WHEN Genre = 'Sci-Fi' THEN 1 ELSE 0 END) > 0
            THEN 'Y'
            ELSE 'N' END AS [Sci-Fi],
       CASE WHEN SUM(CASE WHEN Genre = 'Fiction' THEN 1 ELSE 0 END) > 0
            THEN 'Y'
            ELSE 'N' END AS Fiction,
       CASE WHEN SUM(CASE WHEN Genre = 'Music' THEN 1 ELSE 0 END) > 0
            THEN 'Y'
            ELSE 'N' END AS Music,
       CASE WHEN SUM(CASE WHEN Genre = 'Classics' THEN 1 ELSE 0 END) > 0
            THEN 'Y'
            ELSE 'N' END AS Classics
       FROM @Test1
       GROUP BY Cust_Id, Name) Test1

LEFT JOIN @Test2 Test2 ON Test1.Cust_Id = Test2.Cust_Id
GROUP BY  Test1.Cust_Id,
          Test1.Name,
          CASE WHEN Test2.Cust_Id IS NULL 
               THEN 'N'
               ELSE 'Y' END,
          Test1.[Sci-Fi],
          Test1.Fiction,
          Test1.Music,
          Test1.Classics

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP