Help with select + joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with select + joins

Author

Topic

danielhai
Yak Posting Veteran

50 Posts

Posted - 2002-12-09 : 16:59:23

I'm having trouble with a select, I have one table that holds all my interests for specific contacts, but am having trouble getting a single Interest in the select (there are interestTypes). Kind of hard to explain, It'd be easier if i just posted the SQL and have a look:

I'd like the result set to look like this:


ContactID   ContactName          InterestApparel      InterestVehicle      
----------- -------------------- -------------------- -------------------- 
1           John Doe             Shoes                Toyota
2           Jane Doe             Clothes              NULL

but it's coming out with multiple contacts like so ... 

ContactID   ContactName          InterestApparel      InterestVehicle      
----------- -------------------- -------------------- -------------------- 
1           John Doe             Shoes                NULL
1           John Doe             Shoes                Toyota
1           John Doe             NULL                 NULL
1           John Doe             NULL                 Toyota
2           Jane Doe             Clothes              NULL

set nocount on
declare @ContactInterest table (
	ContactID int,
	InterestID int
)

declare @Contact table (
	ContactID int  PRIMARY KEY,
	ContactName varchar(255)
)

declare @Interest table (
	InterestID int  PRIMARY KEY,
	InterestTypeID int,
	InterestName varchar(255)
)

insert into @Contact select 1, 'John Doe'
insert into @Contact select 2, 'Jane Doe'
insert into @ContactInterest select 1,1
insert into @ContactInterest select 1,4
insert into @ContactInterest select 2,3
insert into @Interest select 1, 1, 'Shoes'
insert into @Interest select 2, 1, 'Socks'
insert into @Interest select 3, 1, 'Clothes'
insert into @Interest select 4, 2, 'Toyota'
insert into @Interest select 5, 2, 'Honda'
insert into @Interest select 6, 2, 'Ford'

select	c.ContactID,
	c.ContactName,
	i.InterestName AS InterestApparel,
	i2.InterestName AS InterestVehicle
from	@Contact c
		LEFT OUTER JOIN @ContactInterest ci on c.ContactID = ci.ContactID
		LEFT OUTER JOIN @Interest i on ci.InterestID = i.InterestID and i.InterestTypeID = 1
		LEFT OUTER JOIN @ContactInterest ci2 on ci2.ContactID = c.ContactID
		LEFT OUTER JOIN @Interest i2 on ci2.InterestID = i2.InterestID and i2.InterestTypeID = 2

set nocount off

I also tried this select, but then it does not include contacts that don't have a specific interest for that type:

select	c.ContactID,
	c.ContactName,
	i.InterestName AS InterestApparel,
	i2.InterestName AS InterestVehicle
from	@Contact c
		LEFT OUTER JOIN @ContactInterest ci on c.ContactID = ci.ContactID
		LEFT OUTER JOIN @Interest i on ci.InterestID = i.InterestID
		LEFT OUTER JOIN @ContactInterest ci2 on ci2.ContactID = c.ContactID
		LEFT OUTER JOIN @Interest i2 on ci2.InterestID = i2.InterestID
where	i.InterestTypeID = 1 and
	i2.InterestTypeID = 2

any help would be GREATLY appreciated.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-09 : 17:27:28

Here's one way:

(by the way: very easy to troubleshoot your problem; thanks for the nice script!)



select	c.ContactID,
	c.ContactName,
	InterestApparel,
	InterestVehicle
from	@Contact c
inner join
  (SELECT ContactID, 
	MAX(CASE WHEN interestTypeID = 1 THEN InterestName ELSE Null END) as InterestApparel,
	MAX(CASE WHEN interestTypeID = 2 THEN InterestName ELSE Null END) as InterestVehicle
   FROM
        @ContactInterest CI
   INNER JOIN 
        @Interest I 
   ON I.InterestID = CI.InterestID
   GROUP BY ContactID) CI
ON
   c.ContactID = CI.ContactID

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-09 : 17:47:18

You may also want to look at this article that shows you how to create a dynamic cross tab query.

http://www.sqlteam.com/item.asp?ItemID=2955

danielhai
Yak Posting Veteran

50 Posts

Posted - 2002-12-09 : 18:12:43

Thanks for the help, I was trying to get away from using subqueries if possible, because this is going to be in a view(would be nice to have it updatable). It looks like my design is bad? Maybe I should do away with the ContactInterest table and put InterestApparel and InterestVehicle inside the Contact table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources