| Author |
 Topic  |
|
|
planetoneautomation
Yak Posting Veteran
89 Posts |
 Posted - 01/22/2013 : 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
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 01/22/2013 : 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
Yak Posting Veteran
89 Posts |
Posted - 01/22/2013 : 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
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 01/22/2013 : 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
Yak Posting Veteran
89 Posts |
Posted - 01/22/2013 : 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
India
47023 Posts |
Posted - 01/22/2013 : 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
Yak Posting Veteran
89 Posts |
Posted - 01/22/2013 : 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
India
47023 Posts |
Posted - 01/22/2013 : 10:55:08
|
cool...glad that you got it sorted out
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Am I Not using JOIN Correctly?
