joining several tables with inner and outer.

1
votes

I'm trying to combine inner and outer join but can't get it to work. I have a total of 7 tables that needs to be joined together in one query. First I only had 6 and had no problem as everything was inner joins. But now I've added a seventh table and can't get it right. I think I need to use OUTER JOIN somehow but don't know how. I will only use 3 tables in this example because I think that I can manage to fix the rest if you help me get started.

List of my tables:

Table1=dbo.kala (This is the 7th table i added.

Table2=dbo.ti

Table3=dbo.ao

SELECT kala.kaldat
From dbo.kala
Where kala.kaldat Between '170407' AND '170410'

The above query returns the following result. 

Result1
|kaldat    |
|2017-04-07|
|2017-04-08|
|2017-04-09|
|2017-04-10|

.

SELECT ti.startdat, ti.artnr, ti.aonr, ti.aopos, ao.prodgr
From dbo.ti
INNER JOIN dbo.ao ON ti.aonr = ao.aonr AND ti.aopos = ao.aopos
Where ti.startdat Between '170407' AND '170410'

The above query returns the following result.

Result2
ti.startdat| ti.artnr| ti.aonr|ti.aopos|ao.prodgr|
2017-04-07 | 123     | 0001   |10      |50       |
2017-04-10 | 456     | 0002   |20      |60       |

The result I want is this (3). 

Result3
kala.kaldat| ti.artnr| ti.aonr|ti.aopos|ao.prodgr|
2017-04-07 | 123     | 0001   |10      |50       |
2017-04-08 |         |        |        |         |
2017-04-09 |         |        |        |         |
2017-04-10 | 456     | 0002   |20      |60       |

The join between dbo.kala and dbo.ti is on kala.kaldat = ti.startdat.

Hopefully you guys understand what I'm trying to get through here. If not i apologize beforehand and will gladly try to explain better. Thanks in advance!

*Note the remaining 4 tables that aren't in this example needs to be joined (INNER?) with dbo.ti.

2
sqljoininner-joinouter-join
Left join is what you're after but if you have any where clause criteria that involves the table on the "right" of the "Left" join, then it needs to be put on the join or your left join simulates a inner join. - xQbert
you are trying to select data that have no index in one of the 3 tables, for that you need to use left join or full outer join depending in what you want to get ---> see i.stack.imgur.com/66zgg.png - Amani Ben Azzouz
I do actually have more where clauses that are connected to to dbo.ti... All I need from dbo.kala is the list of dates. Then I want the rest of the tables to just fill in the information on the correct date. - SisU

2 Answers

2
votes

INNER JOIN dbo.ao ON ti.aonr = ao.aonr AND ti.aopos = ao.aopos

What you want to achieve requires using

LEFT JOIN dbo.ao ON ti.aonr = ao.aonr AND ti.aopos = ao.aopos

1
votes

Select dates from kala, then outer join ti, then outer join ao.

SELECT kala.kaldat, ti.artnr, ti.aonr, ti.aopos, ao.prodgr
FROM dbo.kala
LEFT JOIN dbo.ti ON kala.kaldat = ti.startdat
LEFT JOIN dbo.ao ON ti.aonr = ao.aonr AND ti.aopos = ao.aopos
WHERE kala.kaldat BETWEEN '20170407' AND '20170410';

(You must outer join ao, too, because if the ti record is outer-joined, then its aonr and aopos are null. If you inner joined ao, you'd get no match and thus discard the row from your results.)