Sql Case sensitivity: Challenging Join question

I have a query in which I have 2 tables, but I join one of the tables to twice. I use the result to populate a drop down list

here is the data in table 1 (BaselineControlPairings)

ID SITEID BaselineID ControlID Description

2 4495 2 1 Jones - Jun 07

here is the data in table 2 (BaselineLog)

TESTID SITEID StartDate EndDate DataSetID

4 4495 2007-05-30 2007-06-07 1

5 4495 2007-06-09 2007-06-15 2

I want the query to do the following.. select all records from

BaselineControlPairings where the site id = 4495,

than get the associated BaselineLog record Start and end date where

BaselineControlPairings.BaselineID = BaselineLog.DataSetID

than get the associated BaselineLog record Start and end date where

BaselineControlPairings.ControlID = BaselineLog.DataSetID

Now I string the result together to be displayed.

Here is my query code

Code Snippet

SELECT BaselineControlPairings.TestID

,CONVERT(varchar(12), BaselineLog.StartDate, 110)as BLStart

,CONVERT(varchar(12), BaselineLog.EndDate, 110)as BLEnd

,CONVERT(varchar(12), BaselineLog_1.StartDate, 110)as CTRLStart

,CONVERT(varchar(12), BaselineLog_1.EndDate, 110)as CTRLEnd

,BaselineControlPairings.Description +' Baseline: '+CONVERT(varchar(12)

, BaselineLog.StartDate, 110)+' - '+CONVERT(varchar(12)

, BaselineLog.EndDate, 110)+' -- '+'Control: '+CONVERT(varchar(12)

, BaselineLog_1.StartDate, 110)+' - '+CONVERT(varchar(12), BaselineLog_1.EndDate, 110)as dates

FROM BaselineControlPairings INNERJOIN

BaselineLog AS BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID

INNERJOIN

BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID

WHERE(BaselineControlPairings.siteid = 4495)

here is the result of my query, which we know is wrong because it should contain only 1 result

Jones Jun 07 Baseline: 05-08-2007 - 05-14-2007 -- Control: 05-18-2007 - 05-25-2007
Jones Jun 07 Baseline: 06-09-2007 - 06-15-2007 -- Control: 05-18-2007 - 05-25-2007
Jones Jun 07 Baseline: 05-08-2007 - 05-14-2007 -- Control: 05-30-2007 - 06-07-2007
Jones Jun 07 Baseline: 06-09-2007 - 06-15-2007 -- Control: 05-30-2007 - 06-07-2007

I should only have one record in the result because I should only have the number of results that are in the

BaselineControlPairings with a matching SiteID

I know the issue is occuring in the join, but I have no idea on how to resolve it and I have spent numerous hours on this. any suggestions?

hi, your sample data and your desired output doesn't seem to match.

anyway here's my hunch,

SELECT BaselineControlPairings.ID
, CONVERT(varchar(12), BaselineLog.StartDate, 110) as BLStart
, CONVERT(varchar(12), BaselineLog.EndDate, 110) as BLEnd
, CONVERT(varchar(12), BaselineLog_1.StartDate, 110) as CTRLStart
, CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as CTRLEnd
, BaselineControlPairings.Description + ' Baseline: ' + CONVERT(varchar(12)
, BaselineLog.StartDate, 110) + ' - ' + CONVERT(varchar(12)
, BaselineLog.EndDate, 110) + ' -- ' + 'Control: ' + CONVERT(varchar(12)
, BaselineLog_1.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as dates
FROM BaselineControlPairings INNER JOIN
BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog_1.SiteID INNER JOIN
BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog.SiteID
WHERE (BaselineControlPairings.siteid = 4495)|||Thanks for the reply. I tried your solution, and although better its still broken. I am still getting some duplications but not as many. Also here is my actual code and data:

SELECT BaselineControlPairings.TestID
, CONVERT(varchar(12), BaselineLog.StartDate, 110) as BLStart
, CONVERT(varchar(12), BaselineLog.EndDate, 110) as BLEnd
, CONVERT(varchar(12), BaselineLog_1.StartDate, 110) as CTRLStart
, CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as CTRLEnd

,BaselineControlPairings.Description + ' Baseline: ' + CONVERT(varchar(12), BaselineLog.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog.EndDate, 110) + ' -- '
+ 'Control: ' + CONVERT(varchar(12), BaselineLog_1.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as dates

FROM BaselineControlPairings INNER JOIN
BaselineLog AS BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID INNER JOIN
BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID
WHERE (BaselineControlPairings.siteid = 4495)

The data I am using:

BaselineLog Table

WhoProvisTestID SiteID StartDate EndDate Results DataSetID BLorCTRL

NULL 55 4495 2007-05-27 2007-06-04 NULL 5 0

NULL 56 4495 2007-05-27 2007-06-04 NULL 2 0

NULL 57 4495 2007-05-27 2007-06-04 NULL 3 1

NULL 58 905 2007-05-28 2007-05-31 NULL 3 0

NULL 59 905 2007-05-28 2007-05-31 NULL 1 0

NULL 60 907 2007-05-27 2007-05-29 NULL 17 0

BaselineControlPairings Table

TestID SiteID BaselineID ControlID Description

-- -- -- -- -

25 905 3 1 hgc

26 4495 5 3 df

27 4495 2 5 df

against thes tables I should receive 1 result for the query using ID 905 and 2 for 4495, BUT
for 905 I receive 2 rows both for testID 25, yet there is only 1 test id.

here are the results for 905:
TestID BLStart BLEnd CTRLStart CTRLEnd dates
--
25 05-27-2007 06-04-2007 05-28-2007 05-31-2007 hgc Baseline: 05-27-2007 - 06-04-2007 -- Control: 05-28-2007 - 05-31-2007
25 05-28-2007 05-31-2007 05-28-2007 05-31-2007 hgc Baseline: 05-28-2007 - 05-31-2007 -- Control: 05-28-2007 - 05-31-2007

and for 4495 I should get 2 rows, 1 for test id 26 and one for id 27. Yet I get (2) for 26 and one for 27.

ARGH ! ! !

Results for id 4495
TestID BLStart BLEnd CTRLStart CTRLEnd dates
--
26 05-27-2007 06-04-2007 05-27-2007 06-04-2007 df Baseline: 05-27-2007 - 06-04-2007 -- Control: 05-27-2007 - 06-04-2007
26 05-27-2007 06-04-2007 05-28-2007 05-31-2007 df Baseline: 05-27-2007 - 06-04-2007 -- Control: 05-28-2007 - 05-31-2007
27 05-27-2007 06-04-2007 05-27-2007 06-04-2007 df Baseline: 05-27-2007 - 06-04-2007 -- Control: 05-27-2007 - 06-04-2007

any help is greatly appreciated!|||hi

i'm not sure on what you've meant was still broken, but based on your test data and expected result, if you add BaselineControlPairings.SiteID = BaselineLog_1.SiteID and BaselineControlPairings.SiteID = BaselineLog.SiteID respectively on your joins would result to what you're expecting.

select *
into #BaseLog
from (
select NULL as WhoProvis
, 55 as TestID
, 4495 as SiteID
, '2007-05-27' as StartDate
, '2007-06-04' as EndDate
, NULL as Results
, 5 as DataSetID
, 0 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 56 as TestID
, 4495 as SiteID
, '2007-05-27' as StartDate
, '2007-06-04' as EndDate
, NULL as Results
, 2 as DataSetID
, 0 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 57 as TestID
, 4495 as SiteID
, '2007-05-27' as StartDate
, '2007-06-04' as EndDate
, NULL as Results
, 3 as DataSetID
, 1 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 58 as TestID
, 905 as SiteID
, '2007-05-28' as StartDate
, '2007-06-31' as EndDate
, NULL as Results
, 3 as DataSetID
, 0 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 59 as TestID
, 905 as SiteID
, '2007-05-28' as StartDate
, '2007-06-31' as EndDate
, NULL as Results
, 1 as DataSetID
, 0 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 60 as TestID
, 907 as SiteID
, '2007-05-27' as StartDate
, '2007-06-29' as EndDate
, NULL as Results
, 17 as DataSetID
, 0 as BLorCTRL
) BaseLog

select *
into #BaselineControlPairings
from (
select 25 as TestID
, 905 as SiteID
, 3 as BaselineID
, 1 as ControlID
, 'hgc' as Description
UNION ALL
select 26 as TestID
, 4495 as SiteID
, 5 as BaselineID
, 3 as ControlID
, 'df' as Description
UNION ALL
select 27 as TestID
, 4495 as SiteID
, 2 as BaselineID
, 5 as ControlID
, 'df' as Description
) BaselineControlPairings

SELECT BaselineControlPairings.TestID
, CONVERT(varchar(12), BaselineLog.StartDate, 110) as BLStart
, CONVERT(varchar(12), BaselineLog.EndDate, 110) as BLEnd
, CONVERT(varchar(12), BaselineLog_1.StartDate, 110) as CTRLStart
, CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as CTRLEnd
, BaselineControlPairings.Description + ' Baseline: ' + CONVERT(varchar(12), BaselineLog.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog.EndDate, 110) + ' -- '
+ 'Control: ' + CONVERT(varchar(12), BaselineLog_1.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as dates
FROM #BaselineControlPairings BaselineControlPairings INNER JOIN
#BaseLog BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog_1.SiteID INNER JOIN
#BaseLog BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog.SiteID
WHERE (BaselineControlPairings.siteid = 905)

drop table #BaseLog
drop table #BaselineControlPairings|||Thanks, this fixed it... I think the mistake I was making was not writing to a temp file.|||hi, i'm sorry i don't think that writing to a temp file made the difference. maybe you might have misplaced the ON filter for the SiteID

Wednesday, March 7, 2012

Challenging Join question

No comments:

Post a Comment

Sql Case sensitivity

Blog Archive

About Me