3 table join result problem - 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)

3 table join result problem

Author

Topic

bbowser
Starting Member

43 Posts

Posted - 2010-04-29 : 14:15:12

First of all here's a four table setup.

Create table tblPropertyEvent
(PropertyEventID int Primary Key,
ContactID int,
AlertID int,
PropertyEventNote nvarchar(MAX),
PropertyEventDate datetime,
PropertyEventDivision nvarchar(50),
PropertyEventName nvarchar(50),
Active bit)

Create table tblActionPlan
(ActionPlanID int Primary Key,
PropertyEventID int,
AlertID int,
ActionPlanNote nvarchar(MAX),
ActionPlanBeginDate datetime)

Create table tblOutcome
(OutcomeID int Primary Key,
ActionPlanID int,
OutcomeNote nvarchar(MAX),
OutcomeDate datetime)

Create table tblBusinessAlerts
(AlertID int Primary Key,
AlertName nvarchar(50))

Insert Into tblPropertyEvent
Values ('10', '200', '1', 'New Event Note 1', '20100410', 'IT',
'EventNote1', '1')
Insert Into tblPropertyEvent
Values ('11', '200', '1', 'New Event Note 2', '20100411',
'IT', 'EventNote2', '1')
Insert Into tblPropertyEvent
Values ('12', '200', '2', 'New Event Note 3', '20100412',
'IT', 'EventNote3', '1')
Insert Into tblPropertyEvent
Values ('13', '200', '1', 'New Event Note 4', '20100413',
'IT', 'EventNote4', '0')
Insert Into tblPropertyEvent
Values ('14', '215', '1', 'New Event Note 4', '20100413',
'IT', 'EventNote5', '0')
Insert Into tblPropertyEvent
Values ('15', '215', '1', 'New Event Note 4', '20100413',
'IT', 'EventNote6', '0')

Insert Into tblActionPlan
Values ('100', '10', '1', 'New Action Plan 1', '20100410')
Insert Into tblActionPlan
Values ('101', '11', '1', 'New Action Plan 2', '20100411')
Insert Into tblActionPlan
Values ('102', '12', '2', 'New Action Plan 3', '20100412')
Insert Into tblActionPlan
Values ('103', '13', '1', 'New Action Plan 4', '20100413')
Insert Into tblActionPlan
Values ('104', '13', '1', 'New Action Plan 5', '20100413')
Insert Into tblActionPlan
Values ('105', '13', '1', 'New Action Plan 6', '20100413')
Insert Into tblActionPlan
Values ('106', '13', '1', 'New Action Plan 7', '20100413')

Insert Into tblOutcome
Values ('1', '100', 'New Outcome 1', '20100410')
Insert Into tblOutcome
Values ('2', '101', 'New Outcome 2', '20100411')
Insert Into tblOutcome
Values ('3', '102', 'New Outcome 3', '20100412')
Insert Into tblOutcome
Values ('4', '103', 'New Outcome 4', '20100413')
Insert Into tblOutcome
Values ('5', '103', 'New Outcome 5', '20100414')
Insert Into tblOutcome
Values ('6', '103', 'New Outcome 6', '20100414')
Insert Into tblOutcome
Values ('7', '103', 'New Outcome 7', '20100414')

Insert Into tblBusinessAlerts
Values ('1', 'Alert1')
Insert Into tblBusinessAlerts
Values ('2', 'Alert2')
Insert Into tblBusinessAlerts
Values ('3', 'Alert3')
Insert Into tblBusinessAlerts
Values ('4', 'Alert4')
Insert Into tblBusinessAlerts
Values ('5', 'Alert5')

What I need as a result:
PropertyEventID ContactID AlertID PropertyEventNote PropertyEventDate PropertyEventDivision PropertyEventName Active AlertName ActionPlanID OutcomeID

13 200 1 New Event Note 4 13-Apr IT EventNote4 0 Alert1 103 7

12 200 2 New Event Note 3 12-Apr IT EventNote3 1 Alert2 102 3

11 200 1 New Event Note 2 11-Apr IT EventNote2 1 Alert1 101 2

10 200 1 New Event Note 1 10-Apr IT EventNote1 1 Alert1 100 1

I'm using this query and my results return 4 records with the PropertyEventID of 13. I want to get only Distinct PropertyEventID records.

Query used:
SELECT tblPropertyEvent.PropertyEventID, tblPropertyEvent.ContactID, tblPropertyEvent.AlertID, tblPropertyEvent.PropertyEventNote, CONVERT(VarChar,
tblPropertyEvent.PropertyEventDate, 107) AS PropertyEventDate, tblPropertyEvent.PropertyEventDivision, tblPropertyEvent.PropertyEventName,
tblPropertyEvent.Active, tblBusinessAlerts.AlertName, tblOutcome.ActionPlanID, tblOutcome.OutcomeID
FROM tblOutcome INNER JOIN
tblActionPlan ON tblOutcome.ActionPlanID = tblActionPlan.ActionPlanID RIGHT OUTER JOIN
tblPropertyEvent INNER JOIN
tblBusinessAlerts ON tblPropertyEvent.AlertID = tblBusinessAlerts.AlertID ON tblActionPlan.PropertyEventID = tblPropertyEvent.PropertyEventID
WHERE (tblPropertyEvent.ContactID = '200')
ORDER BY PropertyEventDate DESC

Any help would be greatly appreciated.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-29 : 14:37:14

SELECT tblPropertyEvent.PropertyEventID, tblPropertyEvent.ContactID, tblPropertyEvent.AlertID, tblPropertyEvent.PropertyEventNote, CONVERT(VarChar,
tblPropertyEvent.PropertyEventDate, 107) AS PropertyEventDate, tblPropertyEvent.PropertyEventDivision, tblPropertyEvent.PropertyEventName,
tblPropertyEvent.Active, tblBusinessAlerts.AlertName, tblOutcome.ActionPlanID, Max(tblOutcome.OutcomeID)
FROM tblOutcome INNER JOIN
tblActionPlan ON tblOutcome.ActionPlanID = tblActionPlan.ActionPlanID RIGHT OUTER JOIN
tblPropertyEvent INNER JOIN
tblBusinessAlerts ON tblPropertyEvent.AlertID = tblBusinessAlerts.AlertID ON tblActionPlan.PropertyEventID = tblPropertyEvent.PropertyEventID
WHERE (tblPropertyEvent.ContactID = '200')
Group by tblPropertyEvent.PropertyEventID, tblPropertyEvent.ContactID, tblPropertyEvent.AlertID, tblPropertyEvent.PropertyEventNote, PropertyEventDate, tblPropertyEvent.PropertyEventDivision, tblPropertyEvent.PropertyEventName ,
tblPropertyEvent.Active, tblBusinessAlerts.AlertName, tblOutcome.ActionPlanID
ORDER BY PropertyEventDate DESC

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-04-29 : 14:37:47

Do you only want the row with the greatest OutcomeID?

EDIT: Use pk_bohra's solution if thats the case

bbowser
Starting Member

43 Posts

Posted - 2010-04-30 : 10:21:57

Okay, I'm a little confused. Your solution works in the test DB that I provided but in my production environment it still returns multiple rows with the same PropertyEventID. I'm thinking it has something to do with the relationships I have set up in each of the tables. Do you think this could be causing the problem?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-04-30 : 10:44:27

Can you show the actual data that is giving mutliple rows and the result of the query? Which value is different among the multiple rows?

bbowser
Starting Member

43 Posts

Posted - 2010-04-30 : 10:54:26

Here's the actual data returned. I hope you can make sense out of this. I'm not seeing anything that would return multiple rows. I hope this makes sense.

PropertyEventID ContactID AlertID PropertyEventNote PropertyEventDate PropertyEventDivision PropertyEventName Active AlertName ActionPlanID (No column name)
475 7200 58 They were non compliant with some of their files. Mar 26, 2010 Rental Housing Non Compliant on 3/12/2010 Inspection 1 Non Compliant NULL NULL
467 7200 43 poor management Mar 25, 2010 Homeownership Test Event with Active Checkbox 1 Management NULL NULL
471 7200 43 Poor checkbox management Mar 25, 2010 Rental Housing testing with checkbox 1 Management NULL NULL
463 7200 28 Lots and lots of crime at this place Mar 24, 2010 Administration High Crime 1 High Crime 217 207
463 7200 28 Lots and lots of crime at this place Mar 24, 2010 Administration High Crime 1 High Crime 213 210
463 7200 28 Lots and lots of crime at this place Mar 24, 2010 Administration High Crime 1 High Crime 221 213

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-04-30 : 14:17:02

Use MAX for ActionPlanID as well, and remove it from GROUP BY.

SELECT tblPropertyEvent.PropertyEventID, tblPropertyEvent.ContactID, tblPropertyEvent.AlertID, tblPropertyEvent.PropertyEventNote, CONVERT(VarChar, 
tblPropertyEvent.PropertyEventDate, 107) AS PropertyEventDate, tblPropertyEvent.PropertyEventDivision, tblPropertyEvent.PropertyEventName, 
tblPropertyEvent.Active, tblBusinessAlerts.AlertName, MAX(tblOutcome.ActionPlanID), Max(tblOutcome.OutcomeID)
FROM tblOutcome INNER JOIN
tblActionPlan ON tblOutcome.ActionPlanID = tblActionPlan.ActionPlanID RIGHT OUTER JOIN
tblPropertyEvent INNER JOIN
tblBusinessAlerts ON tblPropertyEvent.AlertID = tblBusinessAlerts.AlertID ON tblActionPlan.PropertyEventID = tblPropertyEvent.PropertyEventID
WHERE (tblPropertyEvent.ContactID = '200')
Group by tblPropertyEvent.PropertyEventID, tblPropertyEvent.ContactID, tblPropertyEvent.AlertID, tblPropertyEvent.PropertyEventNote, PropertyEventDate, tblPropertyEvent.PropertyEventDivision, tblPropertyEvent.PropertyEventName ,
tblPropertyEvent.Active, tblBusinessAlerts.AlertName
ORDER BY PropertyEventDate DESC

bbowser
Starting Member

43 Posts

Posted - 2010-04-30 : 17:13:32

Works like a charm. Thank you so very much for all your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources