Tips with INNER LEFT styles combined

Question

This is probably a simple question for who is the sql database administrator, but I am a guy from C # who just flounders in databases long enough to get them to work when necessary.

I have a new database that I developed that contains only a little data.

I need a query that will be used to generate my view, but I can never figure out how / when to use INNER verses in a LEFT join.

A Packet can have several Request records (one person requests 5 different parts), and each Request record can have different Action records (on hold, cancellation, special order, completion, etc.).

I would like to create a query that creates the following data table:

 SELECT P.EmpID AS Requestor, P.DateStamp AS Submitted, T.Description AS RequestType, L.Description AS Line, R.PartNo, R.Workorder, R.Qty, RT.Description AS ReasonType, S.Description AS Status, A.EmpID AS Stator, A.DateStamp AS Stated, R.MTF FROM Packet AS P LEFT OUTER JOIN Request AS R ON (R.PacketID=P.ID) LEFT OUTER JOIN Action AS A ON (A.RequestID=R.ID) LEFT OUTER JOIN RequestType AS T ON (R.RequestTypeID=T.ID) LEFT OUTER JOIN Line AS L ON (R.LineID=L.ID) LEFT OUTER JOIN ReasonType AS RT ON (R.ReasonTypeID=RT.ID) LEFT OUTER JOIN Status AS S ON (A.StatusID=S.ID) 

This returns 5 rows, but there are several NULL entries for Status , Stator and Stated .

So, I tried to write this using INNER JOIN:

 SELECT P.EmpID AS Requestor, P.DateStamp AS Submitted, T.Description AS RequestType, L.Description AS Line, R.PartNo, R.Workorder, R.Qty, RT.Description AS ReasonType, S.Description AS Status, A.EmpID AS Stator, A.DateStamp AS Stated, R.MTF FROM Packet AS P INNER JOIN Request AS R ON (R.PacketID=P.ID) INNER JOIN Action AS A ON (A.RequestID=R.ID) INNER JOIN RequestType AS T ON (R.RequestTypeID=T.ID) INNER JOIN Line AS L ON (R.LineID=L.ID) INNER JOIN ReasonType AS RT ON (R.ReasonTypeID=RT.ID) INNER JOIN Status AS S ON (A.StatusID=S.ID) 

NULL entries have now disappeared, but now I have only three lines.

How do I know which version should I use? ... or if I use a combination of LEFT and INNER connections?

Answer 1

How do I know which version should I use?

Only you can answer this question. Do you need 5 lines with zero or 3 lines without zeros?

As you noted from your results, LEFT JOIN will return a record, even if there is no corresponding record in the table you are joining (hence the null values). INNER JOIN will only return a record if there is a corresponding record in the table that you enter (which is why you see 3 results instead of 5).

Take a look at this for a great visual explanation of the joins.

Answer 2

How do I know which version should I use?

If the join condition does not give any match, do you want to get NULL values ​​for the missing columns or do you want the row not to be in the result set?

• If you want to use NULL for missing columns, use an outer join.
• If you want the whole row to be absent, use an inner join.

Should I use a combination of LEFT and INNER joins?

You must consider for each connection whether you want it to be an internal or external connection.