Not Returning All Rows - SQL Server Forums

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

Not Returning All Rows

Author

Topic

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2014-06-19 : 13:31:33

I've read and looked at examples where rows will be pulled from a table whether or not rows are in a related table using LEFT OUTER JOIN. But I can't get it to work.

Here's my SQL - works great except that if there's no row in TEST, there's nothing returned for TESTCYCL even though there is a row that meets the WHERE clause criteria:

SELECT

CYCLE.CY_CYCLE AS 'Test Set',

TEST.TS_NAME AS 'Test Name'

FROM TESTCYCL

LEFT OUTER JOIN CYCLE ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID

JOIN TEST ON TS_TEST_ID = TC_TEST_ID

JOIN RELEASE_CYCLES ON TC_ASSIGN_RCYC = RCYC_ID

WHERE RCYC_NAME = '(PQ) Test Cycle 2'

ORDER BY CYCLE.CY_CYCLE

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-19 : 13:38:33

Is TS_TEST_ID, TC_TEST_ID, TC_ASSIGN_RCYC, RCYC_ID or RCYC_NAME a column in the CYCLE table? If it is, that would result in the behavior you are seeing.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-19 : 13:41:59

Alias your columns to make things easier to read. What James said; The WHERE clause is filtering rows out or the join(s), hard to tell.

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2014-06-19 : 13:42:25

No. All fields in the CYCLE table begin with CY_

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-19 : 13:45:49

If you want to pose sample data, I'm sure it'll be easy to see what is happening.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-19 : 13:59:38

I miss read you post. You won't get rows returned if there are no rows in the TEST table as you are INNER JOINING to it. Again, still hard to tell what joins to what, but if you (LEFT?) OUTER JOIN to Test, then that should solve your issue..?

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2014-06-19 : 14:01:03

Assume:

MYTESTSET1 has 3 associated tests TESTA, TESTB, TESTC
MYTESTSET2 has NO associated tests
MYTESTSET3 has 2 associated tests TESTX, TESTY

Then this should be returned by the query:

Test Set Test Name

MYTESTSET1 TESTA
MYTESTSET1 TESTB
MYTESTSET1 TESTC
MYTESTSET2
MYTESTSET3 TESTX
MYTESTSET3 TESTY

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-19 : 14:15:54

If you need more help, please post sample data (in a consumable format) and expected output. If you are not sure how to do that, please follow the links below:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2014-06-19 : 16:31:03

The first link just gives a page indicating an error has occurred.
The second link does not talk about data in a "consumable" format. I don't know what's meant by "consumable".

I think the data I posted above is pretty clear.

In any case, I've simplified the SQL to eliminate a JOIN that supported the WHERE clause and I've eliminated another JOIN to needed to get a name from a related table using an ID. I also eliminated the ORDER BY.

Again, I read and looked at examples of SQL to get all rows from one table even if there is no associated row in another and what everything I read said was that LEFT OUTER JOIN (or RIGHT, depending on which side) would work. It does not. I've tried every combination possible and it does not return a row from TESTCYCL if there's no related row in TEST. Here's the simplified SQL:

SELECT

TESTCYCL.TC_CYCLE_ID AS 'Test Set ID',

TEST.TS_NAME AS 'Test Name'

FROM TESTCYCL

LEFT OUTER JOIN TEST ON TS_TEST_ID = TC_TEST_ID

Any insight would be greatly appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources