How do I get the result I want ? SQL Server 2005

Question

Have a problem with my SQL Server stored procedure query output. Currently using this:

SELECT *
FROM Head H
INNER JOIN Line L ON H.invno = L.invno
INNER JOIN Misc M ON H.invno = M.invno
INNER JOIN QSMU Q ON H.invno = Q.invno
WHERE H.invno = @Invno 
  AND L.deleted = 0 
  AND M.deleted = 0 
  AND Q.deleted = 0

This code purpose is to show everything on my reporting service except the deleted one (filter it using a 'Deleted' column name instead of dropping some row(s).)

Now the problem is, if one of it doesn't have data, e.g: table M doesn't have anything, then the output will be blank.

Iv'e tried changing the logic to OR, but you know, the output will be funny (the 'Deleted = 1' will shown in the output and I don't want that.

You probably want a `LEFT JOIN` although you've got a bigger problem in that your `FROM` clause is pretty useless. — ta.speot.is, Oct 17 '17 at 07:17
you are missing some tables from the select, please provide complete information about the data and table structure. — tukan, Oct 17 '17 at 07:17

Marek Grzenkowicz · Accepted Answer · 2017-10-17T07:43:13.993

Use outer joins and move the deletedchecks to the join conditions (optional in case of the main table).

SELECT *
FROM Head H
LEFT JOIN Line L ON H.invno = L.invno
  AND L.deleted = 0 
LEFT JOIN Misc M ON H.invno = M.invno
  AND M.deleted = 0 
LEFT JOIN QSMU Q ON H.invno = Q.invno
  AND Q.deleted = 0
WHERE H.invno = @Invno

INNER vs OUTER

Head INNER JOIN Line ... only takes the Head rows that have matching Line rows in the second table, i.e. show the orders that have at least 1 line item.

Head LEFT JOIN Line ... takes all the Head rows and appends Line rows (if available; NULLs otherwise), i.e. show all the orders, also the ones with no line items.

It works, thank you so much :) Can you elaborate why do we need to use `LEFT JOIN` instead ? — Emma-Leena Vinter, Oct 17 '17 at 07:36

score 0 · Answer 2 · answered Oct 17 '17 at 07:28

0

Well I don't know the structure of the tables but from your select I see you have inner join.

You should understand what that really means - what you are doing is an intersection of H and M (see Venn diagrams for more).

You should have done LEFT JOINS which shows you all results from H even when missing from L or M or Q (still keeping your select structure):

SELECT *
FROM Head H
LEFT JOIN Line L ON H.invno = L.invno
LEFT JOIN Misc M ON H.invno = M.invno
LEFT JOIN QSMU Q ON H.invno = Q.invno
WHERE H.invno = @Invno 
  AND L.deleted = 0 
  AND M.deleted = 0 
  AND Q.deleted = 0

answered Oct 17 '17 at 07:28

tukan

17,050
1
20
48

1

Even with outer joins, the where clause may still filter out the rows, e.g. if there is no data in `Misc`, `M.deleted` will be NULL. – Marek Grzenkowicz Oct 17 '17 at 07:37
@MarekGrzenkowicz: true. From the question I understood that she wants all that are in `Head` but not in the other tables `and` the additional conditions in the `where` clause. – tukan Oct 17 '17 at 07:53
My understanding was: _take all the rows from every table, except for the ones flagged as deleted_. – Marek Grzenkowicz Oct 17 '17 at 07:55
@MarekGrzenkowicz: which was correct as she accepted your answer. – tukan Oct 17 '17 at 08:11

How do I get the result I want ? SQL Server 2005

2 Answers2

INNER vs OUTER