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_IDWHERE TESTCYCL.TC_ASSIGN_RCYC = 1040 Here's the output:Design Step Name........Step ID.........Step Status.........Cycle IDStep 1...................................................................1464Step 2...................................................................1464Step 1...................23775..........Passed.....................1465Step 2...................23775..........Passed.....................1465Step 1...................23776..........Failed.......................1465Step 2...................23776..........Failed.......................1465Step 1...................................................................1466Step 2...................................................................1466The 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 IDStep 1...................................................................1464Step 2...................................................................1464Step 1...................23775..........Passed.....................1465Step 2...................23776..........Failed.......................1465Step 1...................................................................1466Step 2...................................................................1466I'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_IDWHERE 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_IDWHERE 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.yfrom #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 MVPhttp://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_IDWHERE 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|