SQL DB Question

Question

Question about SQL View. Trying to develop a view from two tables. The two tables have same Primary Keys, execpt the 1st table has all of them, the 2nd has some, but not all. When I INNER Join them, I get a recordset but its not complete, because the 2nd table doesnt have all the records in it. Is there a way in my view to write logic stating that if the key isnt in there int he table #2 to insert a zero so the entire record set is shown in the view? I wan tto show ALL the records in the view even if theres nothing to inner join.

My example below:

SELECT     dbo.Baan_view1b.Number, dbo.Baan_view1b.description, dbo.Baan_view1b.system, dbo.Baan_view1b.Analyst, dbo.Baan_view1b.[User], 
                      dbo.Baan_view1b.[Date Submitted], dbo.Baan_view1b.category, dbo.Baan_view1b.stage, MAX(dbo.notes.percent_developed) AS Expr1
FROM         dbo.Baan_view1b INNER JOIN
                      dbo.notes ON dbo.Baan_view1b.Number = dbo.notes.note_number
GROUP BY dbo.Baan_view1b.Number, dbo.Baan_view1b.description, dbo.Baan_view1b.system, dbo.Baan_view1b.Analyst, dbo.Baan_view1b.[User], 
                      dbo.Baan_view1b.[Date Submitted], dbo.Baan_view1b.category, dbo.Baan_view1b.stage
HAVING      (NOT (dbo.Baan_view1b.stage LIKE 'Closed'))

Answer 1

what you are looking for is the Left Join (left outer join) and not the inner join

SELECT dbo.Baan_view1b.Number, dbo.Baan_view1b.description, dbo.Baan_view1b.system, dbo.Baan_view1b.Analyst, 
        dbo.Baan_view1b.[User], dbo.Baan_view1b.[Date Submitted], dbo.Baan_view1b.category, dbo.Baan_view1b.stage, 
        MAX(dbo.notes.percent_developed) AS Expr1 
FROM dbo.Baan_view1b 
    LEFT OUTER JOIN dbo.notes 
        ON dbo.Baan_view1b.Number = dbo.notes.note_number 
WHERE NOT dbo.Baan_view1b.stage LIKE 'Closed'       
GROUP BY dbo.Baan_view1b.Number, dbo.Baan_view1b.description, dbo.Baan_view1b.system, dbo.Baan_view1b.Analyst, 
        dbo.Baan_view1b.[User], dbo.Baan_view1b.[Date Submitted], dbo.Baan_view1b.category, dbo.Baan_view1b.stage 

Also, changing the HAVING Clause to a WHERE clause makes the query more efficient.

Answer 2

Change the inner join to a left outer join.

(Or a right outer join or a full outer join if you feel fancy.)

Answer 3

Hi,

You need a outer join. This shows all records that have a matching key as well as the ones that don't. The inner join only shows records that have matching join keys.

Enjoy!

Answer 4

You need to do a Left Outer Join as other posters have already mentioned. More information can be found here.

Answer 5

Yes, you can do this. Assuming that baan_view1b has all the records and notes has only some, change

 FROM dbo.Baan_view1b INNER JOIN dbo.notes

to say

 FROM dbo.Baan_view1b LEFT OUTER JOIN dbo.notes

INNER JOIN (or just plain JOIN) tells the database engine to take records from Baan_view1b, match them up with records in notes, and include a row in the output for every pair of records that match. As you have seen, it excludes records from Baan_view1b that don't have matches in the notes table.

LEFT OUTER JOIN instead tells the engine to take ALL the records from Bann_view1b (because it's on the left side of the JOIN keywords). Then, it will match up records from notes wherever it can. However, you are guaranteed a row in the output for every row in the left-hand table regardless of whether it can be matched.

If, as is usual, you asked for column values from both tables, the columns from the table on the right-hand side of the JOIN will have NULL values in the missing rows.