A view of two tables

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 faster

select
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.

A view of two tables

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 faster

select
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.

A question for the experts regarding Rank sql2000

I am trying to rank a table but cannot get the desired
result.
Rank Name Points
1 Bud 82
2 Bill 37
2 Fred 37
3 Sally 26
4 Tim 23
I am getting the above results from this query
SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points
FROM Users U1 INNER JOIN
Users U2 ON U2.Points >= U1.Points
GROUP BY U1.Alias, U1.Points
ORDER BY Rank
I would like to achieve the following result. Is this possible?
Rank Name Points
1 Bud 82
2 Bill 37
2 Fred 37
4 Sally 26
5 Tim 23
Your help would be apreciated.Please provide DDL and sample data...
http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Ross" <andrew.ross@.racingworld.com> wrote in message
news:1124229285.283143.263590@.g43g2000cwa.googlegroups.com...
> I am trying to rank a table but cannot get the desired
> result.
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 3 Sally 26
> 4 Tim 23
> I am getting the above results from this query
> SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points
> FROM Users U1 INNER JOIN
> Users U2 ON U2.Points >= U1.Points
> GROUP BY U1.Alias, U1.Points
> ORDER BY Rank
> I would like to achieve the following result. Is this possible?
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 4 Sally 26
> 5 Tim 23
> Your help would be apreciated.
>|||Do:
SELECT t1.alias,
SUM( CASE WHEN t2.points >= t1.points
THEN 1 ELSE 0 END) -
SUM( CASE WHEN t2.points = t1.points
THEN 1 ELSE 0 END) + 1 AS "rank"
FROM Users t1, Users t2
GROUP BY t1.alias
ORDER BY "rank" ;
The idea is to identify the number of ties within the dataset. For an easy
alternative, consider using the following query with subqueries:
SELECT t1.*,
( SELECT COUNT(t2.points) FROM Users t2
WHERE t2.points >= t1.points) AS "rank",
( SELECT COUNT(t2.points) FROM Users t2
WHERE t2.points = t1.points ) AS "tied counts"
FROM Users t1 ;
You can manipulate this query making it a derived table construct.
Anith|||Try
SELECT COUNT(U2.Points) + 1 AS Rank, U1.Alias, U1.Points
FROM Users U1 INNER JOIN
Users U2 ON U2.Points > U1.Points
GROUP BY U1.Alias, U1.Points
ORDER BY Rank
Regards,
Willson
http://www.wsantoso.net
"Ross" wrote:

> I am trying to rank a table but cannot get the desired
> result.
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 3 Sally 26
> 4 Tim 23
> I am getting the above results from this query
> SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points
> FROM Users U1 INNER JOIN
> Users U2 ON U2.Points >= U1.Points
> GROUP BY U1.Alias, U1.Points
> ORDER BY Rank
> I would like to achieve the following result. Is this possible?
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 4 Sally 26
> 5 Tim 23
> Your help would be apreciated.
>|||Hi Willson
This works great except that it drops the first record (1 Bud
82)
2 Bill 37
2 Fred 37
4 Sally 26
5 Tim 23|||Oh yeah that, change the inner join to left outer JOIN as it's comparing
against those that has bigger point to get the rank. Seriously this is
something that is a lot more efficient to run on the client side rather than
SQL as noted by many other people in your previous thread.
"Ross" wrote:

> Hi Willson
> This works great except that it drops the first record (1 Bud
> 82)
> 2 Bill 37
> 2 Fred 37
> 4 Sally 26
> 5 Tim 23
>|||Thanks that works fine|||create table t (Name varchar(10), Points int)
go
insert into t
select 'Bud', 82 union all
select 'Bill', 37 union all
select 'Fred', 37 union all
select 'Sally', 26 union all
select 'Tim', 23
go
select a.Name,
a.Points,
(select count(distinct x.points) from t x where x.points >= a.points) [Rank]
from t a
order by rank
Rakesh
"Ross" wrote:

> I am trying to rank a table but cannot get the desired
> result.
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 3 Sally 26
> 4 Tim 23
> I am getting the above results from this query
> SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points
> FROM Users U1 INNER JOIN
> Users U2 ON U2.Points >= U1.Points
> GROUP BY U1.Alias, U1.Points
> ORDER BY Rank
> I would like to achieve the following result. Is this possible?
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 4 Sally 26
> 5 Tim 23
> Your help would be apreciated.
>