JOINS for Three Tables - 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

JOINS for Three Tables

Author

Topic

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2014-06-19 : 17:24:09

Table CYCLE has two columns

id
name

Table TEST has two columns

id
name

Table TESTCYCL has three columns

id
cycle_id (reference to id field in CYCLE table)
test_id (reference to id field in TEST table)

I need SQL to get CYCLE.name and TEST.name. If there is no associated row in the TEST table, I still want the CYCLE.name.

SELECT CYCLE.name, TEST.name
FROM CYCLE
JOIN TESTCYCL ON TESTCYCL.cycle_id = CYCLE.id
JOIN TEST ON TEST.id = TESTCYCLE.test_id

This only returns rows from CYCLE where there is a related row in TEST. I need all rows from CYCLE whether there is a row in TEST or not.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-19 : 17:25:33

Change the two "JOIN"s to "LEFT JOIN"s.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-19 : 17:26:07

SELECT CYCLE.name, TEST.name
FROM CYCLE
LEFT JOIN TESTCYCL ON TESTCYCL.cycle_id = CYCLE.id
LEFT JOIN TEST ON TEST.id = TESTCYCLE.test_id

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-19 : 17:26:36

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2014-06-19 : 18:14:13

OK, thanks. That works. Now, when I add a fourth table:

SELECT CYCLE.name, TEST.name
FROM CYCLE
LEFT JOIN TESTCYCL ON TESTCYCL.cycle_id = CYCLE.id
LEFT JOIN TEST ON TEST.id = TESTCYCLE.test_id
LEFT JOIN RELEASECYCLE ON RELEASECYCLE.id = TESTCYCL.assigned_cycle
WHERE RELEASECYCLE.name = 'ABCEFG'

... it reverts back to not getting rows from CYCLE unless there are associated rows in TEST. The LEFT JOIN does not fix the issue in this case. Any idea?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-19 : 18:15:18

You will need to show us some sample data for that as that code will work for what you explained.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-06-19 : 18:35:43

It probably "reverts back" because you don't have a name in RELEASECYCLE that equals 'ABCEFG'.
Did you perhaps mean 'ABCDEFG'?

Edit: Spell correction.

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2014-06-19 : 18:38:45

Sorry, this JOIN was incorrect - correct JOIN below. All working now. Thanks.

LEFT JOIN RELEASECYCLE ON RELEASECYCLE.id = CYCLE.assigned_cycle

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-19 : 19:16:07

quote:
Originally posted by planetoneautomation

Sorry, this JOIN was incorrect - correct JOIN below. All working now. Thanks.

LEFT JOIN RELEASECYCLE ON RELEASECYCLE.id = CYCLE.assigned_cycle

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources