SQL Server optional joins

Question

I have several tables (to be exact, 7) tables I cross join in one another. This part gives me some problems;

Table "Actions"

 -----------------------------------------
| ID | Package ID | Action Type | Message |
 -----------------------------------------
| 40 | 100340     | 0           | OK      |
| 41 | 100340     | 12          | Error   |
| 42 | 100340     | 2           | OK      |

| 43 | 100341     | 4           | OK      |
| 44 | 100341     | 0           | Error   |
| 45 | 100341     | 12          | OK      |
 -----------------------------------------

Table "Packages"

 ----------------------
| ID     | Name        |
 ----------------------
| 100340 | Testpackage |
| 100341 | Package xy  |
 ----------------------

I accomplished cross joingin thm, but when there is no Package with an ID specified in Actions, all actions on that package are completely missing, rather than just leavin Name blank - which is what I'm trying to get.

So, if a reference is missing, just leave the corresponding joined column blank or as an empty string...:

 ----------------------------------------------------------------------
| Package ID | Name        | Action 0 | Action 2 | Action 4 | Action 12 |
 ----------------------------------------------------------------------
| 100340     | Testpackage | OK       | OK       |          | Error     |
| 100341     | Package xy  | Error    |          | OK       | OK        |
 ----------------------------------------------------------------------

How is that possible?

Edit

Sorry, I just saw my example was completety wrong, I updated it how it should look like in the end.

My current query looks something like this (as said above, just an extract as the actual one is about three times as long including even more tables)

SELECT
 PackageTable.ID AS PackageID,
 PackageTable.Name,
 Action0Table.Message  AS Action0,
 Action2Table.Message  AS Action2,
 Action4Table.Message  AS Action4,
 Action12Table.Message AS Action12
FROM
 Packages AS PackageTable LEFT OUTER JOIN
  Actions AS Action0Table ON PackageTable.ID  = Action0Table.PackageID LEFT OUTER JOIN
  Actions AS Action2Table ON PackageTable.ID  = Action2Table.PackageID LEFT OUTER JOIN
  Actions AS Action4Table ON PackageTable.ID  = Action4Table.PackageID LEFT OUTER JOIN
  Actions AS Action12Table ON PackageTable.ID = Action12Table.PackageID
WHERE
 Action0Table.ActionType  = 0 AND
 Action2Table.ActionType  = 2 AND
 Action4Table.ActionType  = 4 AND
 Action12Table.ActionType = 12

Answer 1

So why can't you just do an outer JOIN, such as:

SELECT  `Package ID`, Name, `Action Type`, `Action Date`
   FROM Actions
   LEFT OUTER JOIN Packages
   ON Actions.`Package ID` = Packages.`Package ID`

?

Answer 2

read up on 'outer joins'

Answer 3

Instead of INNER JOIN use LEFT JOIN. That will make it.-

Answer 4

Do you have a 'where' clause which is excluding the missing action records? Even if you use an outer join, your where clause will still be applied and the action records will not be included if they don't match.

EDIT: The problem is your ActionType filters. If there is no matching action record, then ActionType is null, which does not match any of your filters. So, you could add 'or ActionType is null' to your where clause. I don't know your business requirement, but this may include more records than you want.

Answer 5

As what the others has said, I have the same answer.

Just showing u the different result

declare @Actions table(id int , packageid int, actiontype int,dt date)
declare @Packages table(id int,name varchar(50))
insert into @Actions 
    select 40,100340,0,'2009/01/01 3:00pm' union all
    select 41,100340,12,'2009/01/01 5:00pm' union all
    select 42,100340,2,'2009/01/01 5:30pm' union all
    select 43,100341, 4,'2009/01/02 8:00am'
insert into @Packages 
    select 100340,'Testpackage'

Left outer join query

select a.packageid,p.name,a.actiontype,a.dt
from @Actions a
left join @Packages p
on a.packageid = p.id

Full join(in this case u will get the same result)

select a.packageid,p.name,a.actiontype,a.dt
from @Actions a
full join @Packages p
on a.packageid = p.id

Output:

packageid   name actiontype dt
100340  Testpackage 0 2009-01-01
100340  Testpackage 12 2009-01-01
100340  Testpackage 2 2009-01-01
100341  NULL 4 2009-01-02

Inner join query(which u don't want)

select a.packageid,p.name,a.actiontype,a.dt
from @Actions a
join @Packages p
on a.packageid = p.id

Output:

packageid   name actiontype dt
100340  Testpackage 0 2009-01-01
100340  Testpackage 12 2009-01-01
100340  Testpackage 2 2009-01-01

Answer 6

Your are left (outer) joining on the packages table. This means that if the record is not in the packages table (the table on the left side of the join condition) then don't include it in the final result.

You could right (outer) join on the action table in which case you will get all of the action records whether or not they have a match in the package table.

You could do a full (outer) join, in other words, give me all of the records in both tables.

And finally you can do an inner join, or the records which are present in both tables.

You may find it helpful to picture a Venn diagram here with the left table as the left circle and the right table as the right circle. The inner join thus represents the overlapping region of the diagram.

So, to answer your question, you are going to need to tweak your joins to be full outer joins or right joins depending on whether or not you want to see packages without actions or not. And you are going to need to adjust your where clause to include null actions as has been suggested by many other posters. Though you may want to add an additional clause to that where expression which excludes records where all of the actions are null. Plus, as your example is written you are only going to see packages where the actions on that package are 0, 2, 4 and 12; which sounds incorrect given the information you've provided.

SELECT
 PackageTable.ID AS PackageID,
 PackageTable.Name,
 Action0Table.Message  AS Action0,
 Action2Table.Message  AS Action2,
 Action4Table.Message  AS Action4,
 Action12Table.Message AS Action12
FROM
 Packages AS PackageTable
  RIGHT OUTER JOIN Actions AS Action0Table ON 
     PackageTable.ID  = Action0Table.PackageID
  RIGHT OUTER JOIN Actions AS Action2Table ON
     PackageTable.ID  = Action2Table.PackageID
  RIGHT OUTER JOIN Actions AS Action4Table ON
     PackageTable.ID  = Action4Table.PackageID
  RIGHT OUTER JOIN Actions AS Action12Table ON
     PackageTable.ID = Action12Table.PackageID
WHERE
 (Action0Table.ActionType  = 0 OR Action0Table.ActionType IS NULL) AND
 (Action2Table.ActionType  = 2 OR Action2Table.ActionType IS NULL) AND
 (Action4Table.ActionType  = 4 OR Action4Table.ActionType IS NULL) AND
 (Action12Table.ActionType = 12 OR Action12Table.ActionType IS NULL) AND
 NOT (Action0Table.ActionType IS NULL AND Action2Table.ActionType IS NULL AND
  Action4Table.ActionType IS NULL AND Action12Table.ActionType IS NULL)

You will need to remove that final NOT clause if you want to see packages without any of those actions. Also, depending on the quality of the data you may begin receiving duplicate records once you start including null values; in which case your problem has become a lot harder to solve and you will need to get back to us.