Am I Not using JOIN Correctly? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Am I Not using JOIN Correctly?

Author

Topic

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2013-01-22 : 08:51:23

In the following SQL, I think I have a JOIN wrong somehow but I've tried what I know to try to no avail. Here's the SQL:

SELECT
 DESSTEPS.DS_STEP_NAME as 'Design Step Name',
 STEP.ST_ID AS 'Step ID',
 STEP.ST_STATUS as 'Step Status' ,
 CYCLE.CY_CYCLE_ID as 'Cycle ID'
FROM CYCLE
 LEFT JOIN TESTCYCL ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
 LEFT JOIN TEST ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
 LEFT JOIN RUN ON TEST.TS_TEST_ID = RUN.RN_TEST_ID AND RN_EXECUTION_DATE =  (SELECT MAX(RN_EXECUTION_DATE) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID)  AND RN_EXECUTION_TIME =  (SELECT MAX(RN_EXECUTION_TIME) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID AND RN_EXECUTION_DATE =  (SELECT MAX(RN_EXECUTION_DATE) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID))
 LEFT JOIN STEP ON STEP.ST_RUN_ID = RUN.RN_RUN_ID
 LEFT JOIN DESSTEPS ON DESSTEPS.DS_TEST_ID = TEST.TS_TEST_ID --AND DESSTEPS.DS_ID = STEP.ST_DESSTEP_ID
WHERE TESTCYCL.TC_ASSIGN_RCYC =  1040

Here's the output:

Design Step Name........Step ID.........Step Status.........Cycle ID
Step 1...................................................................1464
Step 2...................................................................1464
Step 1...................23775..........Passed.....................1465
Step 2...................23775..........Passed.....................1465
Step 1...................23776..........Failed.......................1465
Step 2...................23776..........Failed.......................1465
Step 1...................................................................1466
Step 2...................................................................1466

The two lines in bold should not be there and don't represent actual data in the tables but are "selected" due to some JOIN issue I believe.

Here's what the actual output should be:

Here's the output:

Design Step Name........Step ID.........Step Status.........Cycle ID
Step 1...................................................................1464
Step 2...................................................................1464
Step 1...................23775..........Passed.....................1465
Step 2...................23776..........Failed.......................1465
Step 1...................................................................1466
Step 2...................................................................1466

I've now spent in excess of 10 hours trying to resolve this one issue. If anyone can help, it'd would be hugely appreciated.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-22 : 09:10:16

This is happening because one of the joins - I don't know which one - is a one to many (one to two) relationship.

I would go about debugging it by removing all the joins and starting with just two tables.

SELECT
	CYCLE.CY_CYCLE_ID as 'Cycle ID'
FROM
	CYCLE
	INNER JOIN TESTCYCL ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
WHERE 
	TESTCYCL.TC_ASSIGN_RCYC =  1040

See if the data you get makes sense - whether there are duplicate rows. (Note, I changed the join to an inner join. Because of the where clause, LEFT JOIN does not get you any extra rows)

If that looks reasonable, add the next table.

SELECT
	CYCLE.CY_CYCLE_ID as 'Cycle ID'
FROM
	CYCLE
	INNER JOIN TESTCYCL ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
	LEFT JOIN TEST ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
WHERE 
	TESTCYCL.TC_ASSIGN_RCYC =  1040

Again, see if there are duplicate rows. If there are, it is the latest join that has the one-to-many relationship. Investigate why that is so and you will need to fix that.

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2013-01-22 : 09:16:40

To add, here's what the data in the STEP and DESSTEPS table looks like:

DESSTEPS Table:

Row 1: Step 1 is Design Step Name (for Cycle ID 1464)
Row 2: Step 2 is Design Step Name (for Cycle ID 1464)
Row 3: Step 1 is Design Step Name (for Cycle ID 1465)
Row 4: Step 2 is Design Step Name (for Cycle ID 1465)
Row 5: Step 1 is Design Step Name (for Cycle ID 1466)
Row 6: Step 2 is Design Step Name (for Cycle ID 1466)

DESSTEPS Table:

Row 1: 23775 is Step ID, Passed is Step Status (for Cycle ID 1465)
Row 2: 23776 is Step ID, Failed is Step Status (for Cycle ID 1465)

So as you can see, there are only 2 rows in DESSTEPS yet there are four rows selected. This is the problem.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-22 : 09:52:06

I am sorry, but I don't have enough information to say exactly why you are getting duplicate rows. I can show you the following example, where I have only one row in table #A and table #C, yet the query produces two rows in the results. This is because of the duplicate rows in table #B. As I had suggested in my previous posting, to diagnose this, I would start out with #A alone, then add #B and so on until I am able to see where you get unexpected number of rows:

create table #a(ida int, x varchar(32));
create table #b(ida int, idc int);
create table #c(idc int, y varchar(32));

insert into #a values (1,'abcd');
insert into #b values (1,1),(1,1);
insert into #c values (1,'xyz');

select a.x,
       c.y
from   #a a
       inner join #b b
            on  a.ida = b.ida
       inner join #c c
            on  c.idc = b.idc;
            
drop table #a, #b, #c;

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2013-01-22 : 10:00:49

quote:
Originally posted by James K

This is happening because one of the joins - I don't know which one - is a one to many (one to two) relationship.

Thanks - great advice. I've narrowed down the issue to one JOIN. Its the addition of this JOIN that creates the additional rows.

LEFT JOIN STEP ON STEP.ST_RUN_ID = RUN.RN_RUN_ID

Unfortunately, without that JOIN "STEP.ST_ID" could not be bound. So now, I'm darned if I do and darned if I don't. I've determined it's this JOIN that's problematic but I'm not sure how to bind "STEP.ST_ID" without using this JOIN.

Ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-22 : 10:05:48

i think reason is because you're not using stepid in join. so its doing kind of cartesian product with step table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2013-01-22 : 10:36:34

Absolutely correct! This SQL is now working. The JOIN's in bold are what I had to change. Problem resolved.

SELECT
 DESSTEPS.DS_STEP_NAME as 'Design Step Name',
 DESSTEPS.DS_DESCRIPTION as 'Design Step Description',
 STEP.ST_ID AS 'Step ID',
 STEP.ST_STATUS as 'Step Status' ,
 CYCLE.CY_CYCLE_ID as 'Cycle ID'
FROM
        CYCLE
 INNER JOIN TESTCYCL ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
 LEFT JOIN TEST ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
 LEFT JOIN RUN ON TEST.TS_TEST_ID = RUN.RN_TEST_ID AND RN_EXECUTION_DATE =  (SELECT MAX(RN_EXECUTION_DATE) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID)  AND RN_EXECUTION_TIME =  (SELECT MAX(RN_EXECUTION_TIME) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID AND RN_EXECUTION_DATE =  (SELECT MAX(RN_EXECUTION_DATE) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID))

 LEFT JOIN DESSTEPS ON DESSTEPS.DS_TEST_ID = TEST.TS_TEST_ID
 LEFT JOIN STEP ON STEP.ST_DESSTEP_ID = DESSTEPS.DS_ID AND STEP.ST_RUN_ID = RUN.RN_RUN_ID
WHERE TESTCYCL.TC_ASSIGN_RCYC =  1040

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-22 : 10:55:08

cool...glad that you got it sorted out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources