CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Emma-Leena Vinter
Emma-Leena Vinter

Reputation: 33

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

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.

Upvotes: 2

Views: 61

Answers (2)

Marek Grzenkowicz
Marek Grzenkowicz

Reputation: 17353

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.

Upvotes: 3

tukan
tukan

Reputation: 17347

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

Upvotes: 0

Related Questions