Sql left join and inner join

Question

0.00/5 (No votes)

See more:

Here is the SQL to my query (the link portion only). What is frustrating is I had linked this successfully and exited without saving. It has been a weekend and 2 working days and I have not been able to figure out the correct linking order. My left table (listed first after from) VTG_COPO_HIST contains all of the lines I need on it's own. When linked to the second table by order number, all of the required lines are there. However, I will need to link further by part number to pull in part number specific field points. When I link by part number with an inner join, I lose one row as that particular row in my VTG table is null for that field. So I change the join of the part # level to Left Join. The missing row still does not pull in. My guess is that I have something contradictory in my code to prevent it from including that null line.

SQL

FROM {oj VMFG.dbo.VTG_COPO_HIST LEFT OUTER JOIN VMFG.dbo.INVENTORY_TRANS ON VTG_COPO_HIST.PART_ID_NEW = INVENTORY_TRANS.PART_ID}
WHERE VTG_COPO_HIST.ORDER_ID = INVENTORY_TRANS.CUST_ORDER_ID AND ((VTG_COPO_HIST.ORDER_ID='h111212'))

What I have tried:

I have written a list of possible orders and combos of inner/outer joins and tried every possible combination. I have googled and seen in forums that it tends to be having a contradictory "where" statement, which if I have, I cannot find. I am much more comfortable with VBA, SQL is completely new for me.

Posted 18-Mar-19 4:12am

Member 14186603

Updated 17-Jun-20 20:11pm

Herman<T>.Instance

v2

Add a Solution

Comments

OriginalGriff 18-Mar-19 10:59am

Create a small "test DB" which contains a few lines in each table.
Then show us those lines, the query you run against it, and the results.
Then explain what is wrong with the results, and what exactly you do expect to happen.

Since we have no access at all to your data, and have no idea exactly what you are expecting, we need this in order to work out what to suggest.

Use the "Improve question" widget to edit your question and provide better information.

ZurdoDev 18-Mar-19 13:58pm

How can we help? You have something wrong in your sql but how would we know what it is? It might be in your WHERE clause.

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

W∴ Balboos, *GHB* · Answer 1 · 2019-03-19T05:20:00

Not being able to see your table, I can give you the "How" for designing your joins and a why you will (or will not) eliminate rows.

So far, you have discovered how an inner join with the third table will wipe out some rows and correctly realized that you need a left-join to maintain all of the base rows from your primary table.

A Left join will show all selected rows in the top-level table and either a value or NULL for those in the JOINed table. Now, when adding the third table, you need to be care about the ON component of the join. Let's presume that your third table is looking for values in the second table. If you use an INNER JOIN, wherever it fails it will remove the entire row because it's eliminating the second table value. If you use a LEFT JOIN, and match the value to the table where the linkage is to found. You need to target that ON to the correct table.

Your stripped down version (the SELECT portion would have been very helpful in creating a more specific answer). A logical layout would be HISTORY->ORDER->PART.

So, rehashing: You request your preferred customer list and get orders for those customers and then the components for each order. At least that's how I'd lay it out.

But, as noted in the comment preceding this, you do not give the table schema and we've not enough information.

rammanusani · Answer 2 · 2019-03-26T08:26:00

Solution 2

Looks like the issue in where clause

Posted 26-Mar-19 8:26am

rammanusani

Sql left join and inner join

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Sql left join and inner join

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us