Joining on Tables without excess data

Transact-SQL
1

There is a table called TBLPARTS P with a primary key P.PRIMLINK. It contains basic info about the part.

There is another table called TBLBARCODE B with the foreign key B.INVLINK=P.PRIMLINK that shows various part's barcode symbols used (vendor's, store's, etc).

There is another table called TBLLOCATION L with the foreign key L.INVLINK=P.PRIMLINK that shows various part's location and quantity information. Store 16 could have a part on the shelf, in stock, and on a quick-pick counter.

Last, I've got a table called TBLLOTS T with the foreign key T.INVLINK=P.PRIMLINK that is used for large spools of wire, rope, chain, etc. that are in some sort of partial state. Example: A customer buys 500-ft of cable on a 1000-ft spool, so that spool no longer has QTY=1. It gets converted into a lot of 1000 pieces, so that the result is 500 pieces. If another customer comes in and wants to buy 700-ft of cable, now we have 2 lots of the same part number: one at 500 pieces, and the other at 300 pieces. Are you following this?

I need to display just a little information about each of these tables.

I can join them using the INVLINK=PRIMLINK, filter them using STORE=16, but could still be looking at 2 to 4 records for a given barcode and 3 or 4 different lot numbers. When I do my join I could be looking at 16 duplicate looking rows for the same part number.

My SQL understanding is fine for straight-line SELECT, INSERT, and UPDATE commands, but I always seem to get really confused when it comes to JOIN statements.

What is a good way to rewrite this SELECT statement?

SELECT L.INVLINK, P.CATALOG, P.VENDOR, B.UPC, L.BIN_LOCATION, L.IN_STOCK-L.QTY_SHIPPED AS OnShelf, L.IN_STOCK, L.QTY_SHIPPED, L.LOC_TYPE
FROM TBLLOCATION L 
LEFT JOIN TBLBARCODE B ON (L.INVLINK=B.INVLINK)
LEFT JOIN TBLPARTS P ON (P.PRIMLINK=L.INVLINK)
LEFT JOIN TBLLOTS T ON (L.INVLINK=T.INVLINK)
WHERE L.STORE=16 AND I.VENDOR='TES' AND I.PRIMARY='P'
ORDER BY I.VENDOR, I.CATALOG, L.BIN_LOCATION, L.INVLINK, B.UPC;

We are actually using an IBM DB2 database, but the SQL I am trying to learn is not anything focused on Microsoft SQL or IBM's SQL.

2

One way would be to use OUTER APPLY instead of LEFT JOIN as I have shown below. Since you want only one row from TBLBARCODE, and if there can be multiple rows that match the join condition of L.INVLINK = B.INVLINK, you have to decide which of those rows you want to use to pick the value of the column that you are interested in, namely B.UPC. That is where the ORDER BY clause within the outer apply clause comes into play

....       
FROM    TBLLOCATION L
        --LEFT JOIN TBLBARCODE B ON ( L.INVLINK = B.INVLINK )
        OUTER APPLY 
        (
			SELECT TOP (1) B.UPC
			FROM TBLBARCODE B
			WHERE L.INVLINK = B.INVLINK
			ORDER BY B.SomeColumnHere
		) AS B
....

Another option would be to use an aggregate function (e.g. MAX(B.UPC), together with a group by clause. In your case, it may be better to use the OUTER APPLY as I have shown above.

1 Like