Compare INT to Primary Key - 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 2005 Forums

Transact-SQL (2005)

Compare INT to Primary Key

Author

Topic

bbowser
Starting Member

43 Posts

Posted - 2009-10-12 : 09:42:08

I have a tblContacts that I have a Primary Key on ContactID (int) and I'm associating other contacts from the same table with other INT columns. I need to compare the tblContacts.ContactID with the ManagementCoID (INT) column and return the BusinessName. Here is my query. Please help, I'm at my wits end.

SELECT tblContacts.BusinessName, tblContacts.Phone, tblContacts.City, tblContacts.State,
(SELECT BusinessName
FROM tblContacts
WHERE (ContactID = ManagementCoID)) AS ManagementCo
FROM tblContacts Inner Join tblContactsToRating ON
tblContacts.ContactID = tblContactsToRating.ContactID
WHERE (tblContactsToRating.RatingsID = '33') OR
(tblContactsToRating.RatingsID = '38')

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-10-12 : 09:46:30

I guess ManagementCoID is from tblContacts. If the following is incorrect, please post some sample data and your expected result set.


SELECT c.BusinessName, c.Phone, c.City, c.State, 
case when c.ContactID = ManagementCoID then BusinessName end as BusinessName
FROM tblContacts c
Inner Join tblContactsToRating cr 
ON c.ContactID = cr.ContactID
WHERE (cr.RatingsID = '33') OR (cr.RatingsID = '38')

bbowser
Starting Member

43 Posts

Posted - 2009-10-12 : 10:17:13

Yes, ManagementCoID is from the tblContacts table. I tried your solution and it didn't return the BusinessName It just returned NULL even though I have the INT value for the ManagementCOID in the table.

Let's say my table is like this

ContactID BusinessName Address1 ManagementCoID
2212 Diamond Minds 123 Main Street 4218
4218 Lakeland, Inc 419 S. Haven NULL

I want to return:
BusinessName Address1 BusinessName2
Diamond Minds 123 Main Street Lakeland, Inc.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-10-12 : 11:48:20

Ok, thats better, try this instead then:


SELECT c.BusinessName, c.Phone, c.City, c.State, t2.BusinessName
FROM tblContacts c
Inner Join tblContactsToRating cr 
ON c.ContactID = cr.ContactID
left join (select BusinessName, ManagementCoID
			from  tblContacts) t2
ON t2.ManagementCoID = c.ContactID
WHERE (cr.RatingsID = '33') OR (cr.RatingsID = '38')

bbowser
Starting Member

43 Posts

Posted - 2009-10-12 : 12:41:15

Okay, I tried that solution and it didn't work either. I'm posting the entire query so you can see that if it helps you. The results that came back for the t2.BusinessName AS ManagementCo was NULL and I've triple checked that I do have an INT number for the ManagementCoID in several records. I'm using Visual Studio 2005 for a SQL report if that makes any difference. Thank you for all your help.

SELECT tblContacts_1.BusinessName, tblContacts_1.Phone, tblContacts_1.City, tblContacts_1.State, tblRatings.RatingName, tblActionPlan.ActionPlanNote,
tblActionPlan.UserID, tblOutcome.OutcomeNote, tblActionPlan.ActionPlanUpdated, tblPropertyEvent.PropertyEventDivision,
tblPropertyEvent.PropertyEventName, t2.BusinessName AS ManagementCo
FROM tblOutcome RIGHT OUTER JOIN
tblContacts AS tblContacts_1 INNER JOIN
tblContactsToRating ON tblContacts_1.ContactID = tblContactsToRating.ContactID INNER JOIN
tblRatings ON tblContactsToRating.RatingsID = tblRatings.RatingsID INNER JOIN
tblActionPlan INNER JOIN
tblPropertyEvent ON tblActionPlan.PropertyEventID = tblPropertyEvent.PropertyEventID ON tblContacts_1.ContactID = tblPropertyEvent.ContactID ON
tblOutcome.ActionPlanID = tblActionPlan.ActionPlanID LEFT OUTER JOIN
(SELECT BusinessName, ManagementCoID
FROM tblContacts) AS t2 ON t2.ManagementCoID = tblContacts_1.ContactID
WHERE (tblContactsToRating.RatingsID = '33') OR
(tblContactsToRating.RatingsID = '38')

**** I hope this helps ****

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-10-13 : 06:05:35

From the data you gave me, the first query works, without more sample data, I can't tell you what is wrong with your query.

This works:


declare @tblContacts as table (ContactID int, BusinessName varchar(100), Address1 varchar(100), ManagementCoID int)
insert into @tblContacts
select 2212, 'Diamond Minds', '123 Main Street', 4218
union select 4218, 'Lakeland, Inc', '419 S. Haven', NULL

SELECT c.BusinessName, c.Address1, t2.BusinessName
FROM @tblContacts c
left join (select BusinessName, ManagementCoID
			from  @tblContacts) t2
ON t2.ManagementCoID = c.ContactID

So I woould suggest it was a data issue with one of the other tables in your query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources