SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Am I Not using JOIN Correctly?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 01/22/2013 :  08:51:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3586 Posts

Posted - 01/22/2013 :  09:10:16  Show Profile  Reply with Quote
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.
Go to Top of Page

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 01/22/2013 :  09:16:40  Show Profile  Reply with Quote
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.



Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3586 Posts

Posted - 01/22/2013 :  09:52:06  Show Profile  Reply with Quote
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;
Go to Top of Page

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 01/22/2013 :  10:00:49  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/22/2013 :  10:05:48  Show Profile  Reply with Quote
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/

Go to Top of Page

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 01/22/2013 :  10:36:34  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/22/2013 :  10:55:08  Show Profile  Reply with Quote
cool...glad that you got it sorted out

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.56 seconds. Powered By: Snitz Forums 2000