Which Join - 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

Transact-SQL (2000)

Which Join

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2006-04-24 : 00:33:27

I have a complicated query, but I am only going to paste a portion of it here. I have one table that holds all the phone numbers for a person (EntityPhone) and another table that holds the different phone types (CategoryElement). Every join I try is not working. Sometimes I may get every person multiplied by the number of types. Sometimes I may only get the people that have all three phone types with data. What I need is every person listed whether they have phones or not, but if they do, I need the number listed under that type. Does anyone have any suggestions on what I am NOT getting here... or NOT trying! Thx so much!!

SELECT
E.ID as "EntityID",
E.LastName as "LastName",
EPHome.Number as "HomePhone",
EPBusiness.Number as "BusinessPhone",
EPMobile.Number as "MobilePhone"

FROM Entity as E
FULL JOIN EntityPhone as EPHome
ON E.ID = EPHome.EntityID
LEFT JOIN CategoryElement as CE
ON (EPHome.PhoneTypeCID = CE.ID) AND (UPPER(CE.Description) = 'HOME')

FULL JOIN EntityPhone as EPBusiness
ON E.ID = EPBusiness.EntityID
LEFT JOIN CategoryElement as CEBusiness
ON (EPBusiness.PhoneTypeCID = CEBusiness.ID) AND (UPPER(CEBusiness.Description) = 'BUSINESS')

FULL JOIN EntityPhone as EPMobile
ON E.ID = EPMobile.EntityID
LEFT JOIN CategoryElement as CEMobile
ON (EPMobile.PhoneTypeCID = CEMobile.ID) AND (UPPER(CEMobile.Description) = 'MOBILE')

WHERE E.EntitySubTypeENUM = 2
ORDER BY E.LastName

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-24 : 01:31:47

"What I need is every person listed whether they have phones or not, but if they do, I need the number listed under that type"
use LEFT JOIN

SELECT  *
FROM    Entity as E LEFT JOIN EntityPhone as EPHome
        ON E.ID = EPHome.EntityID
        . . . .

JAdauto
Posting Yak Master

160 Posts

Posted - 2006-04-24 : 09:47:16

I thought that would work as well. However, I am joining the same tables three times in this example (Home, Business, Mobile). So it is multiplying my entity accounts several times instead of returning them only one time.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-04-24 : 10:08:33

give us some sample input data and some matching expected results to move this to a solution faster.

JAdauto
Posting Yak Master

160 Posts

Posted - 2006-04-24 : 10:38:47

CREATE TABLE [dbo].[Entity] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EntityPhone] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[EntityID] [int] NOT NULL ,
[PhoneTypeCID] [int] NOT NULL ,
[Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CategoryElement] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

Insert into Entity (Lastname) Values ('Donald')
Insert into Entity (Lastname) Values ('Fisher')
Insert into Entity (Lastname) Values ('Holmes')

Insert INTO CategoryElement (Description) Values ('Home')
Insert INTO CategoryElement (Description) Values ('Business')
Insert INTO CategoryElement (Description) Values ('Mobile')
Insert INTO CategoryElement (Description) Values ('Fax')

Insert into EntityPhone (EntityID,PhoneTypeCID,Number) Values (1,1,'111-111-1111')
Insert Into EntityPHone (EntityID,PhonetypeCID,Number) Values (1,2,'222-222-2222')
Insert Into EntityPHone (EntityID,PhonetypeCID,Number) Values (1,3,'333-333-3333')
Insert Into EntityPHone (EntityID,PhonetypeCID,Number) Values (2,1,'888-888-8888')
Insert Into EntityPHone (EntityID,PhonetypeCID,Number) Values (3,2,'666-666-6666')
Insert Into EntityPHone (EntityID,PhonetypeCID,Number) Values (3,1,'555-555-5555')

And here is the SQL statement again without the where stuff:
SELECT
E.ID as "EntityID",
E.LastName as "LastName",
EPHome.Number as "HomePhone",
EPBusiness.Number as "BusinessPhone",
EPMobile.Number as "MobilePhone"

FROM Entity as E
LEFT JOIN EntityPhone as EPHome
ON E.ID = EPHome.EntityID
LEFT JOIN CategoryElement as CE
ON (EPHome.PhoneTypeCID = CE.ID) AND (UPPER(CE.Description) = 'HOME')

LEFT JOIN EntityPhone as EPBusiness
ON E.ID = EPBusiness.EntityID
LEFT JOIN CategoryElement as CEBusiness
ON (EPBusiness.PhoneTypeCID = CEBusiness.ID) AND (UPPER(CEBusiness.Description) = 'BUSINESS')

LEFT JOIN EntityPhone as EPMobile
ON E.ID = EPMobile.EntityID
LEFT JOIN CategoryElement as CEMobile
ON (EPMobile.PhoneTypeCID = CEMobile.ID) AND (UPPER(CEMobile.Description) = 'MOBILE')

ORDER BY E.LastName

What you will see is that Donald (who has three phone records) is coming back from this query 27 times. Fisher (who has one phone record) is only coming up once as he should. And then Holmes (who has two phone records is coming up 8 times. I should only see Donald, Fisher and Holmes one time each with their phone numbers in the column if they have them. If no phone number, it should be blank. I dont know why I am getting each person so many times. I think it must have something to do with the join on CategoryElement, but I cannot seem to get them right. Thanks so much for looking at this.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-04-24 : 10:45:50

SELECT
E.ID as "EntityID",
E.LastName as "LastName",
(SELECT EPHome.Number
FROM EntityPhone as EPHome
INNER JOIN CategoryElement as CE
ON (CE.ID = EPHome.PhoneTypeCID) AND (UPPER(CE.Description) = 'HOME')
WHERE EPHome.EntityID = E.ID) as "HomePhone",
(SELECT EPBusiness.Number FROM EntityPhone as EPBusiness
INNER JOIN CategoryElement as CEBusiness
ON (EPBusiness.PhoneTypeCID = CEBusiness.ID) AND (UPPER(CEBusiness.Description) = 'BUSINESS')
WHERE EPBusiness.EntityID = E.ID
) as "BusinessPhone",
(SELECT EPMobile.Number FROM EntityPhone as EPMobile
INNER JOIN CategoryElement as CEMobile
ON (EPMobile.PhoneTypeCID = CEMobile.ID) AND (UPPER(CEMobile.Description) = 'MOBILE')
WHERE EPMobile.EntityID = E.ID) as "MobilePhone"
FROM Entity as E
ORDER BY E.LastName

JAdauto
Posting Yak Master

160 Posts

Posted - 2006-04-24 : 22:31:26

When I use that query and the data I provide above (minus the Where since I did not build that field into my sample table), I get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Is what I am trying not possible with this data structures? Thx

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-04-25 : 01:06:22

Duh ... sorry, guess I should test what I write before I post it. You need an INNER JOIN of course, I have edited my previous post acordingly (and tested it against your data)

JAdauto
Posting Yak Master

160 Posts

Posted - 2006-04-25 : 09:01:54

That works amazing!! I am so confused how that works, but the previous did not. It seems like they are saying the same thing, just in a different location. I even changed my original to INNER join and it did not work. Is it because in the original I was doing the two joins (to EntityPhone AND to CategoryElement) and in this one there is one join and one where condition? I cannot thank you enough for this solution!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources