Select Statement - Multiple Tables (3)

[Moordoom]

Trying to create a SQLCommand in VB.NET to do a SELECT from 3 different tables.

Table1 - Shipper
Contains - LotNum, CustOrderNum

Table2 - CustOrder
Contains - CustOrderNum, CustNum

Table3 - Customer
Contains - CustNum, CustName

The tables contain more, but these are the columns I am working with.

The User scans a Lot Number (LotNum) and I want it to display the Customer Name (CustName) in a Label or Text Box.

Was thinking is was something like this...

SELECT LotNum, CustOrderNum from Shipper INNER JOIN CustOrder ON CustOrderNum INNER JOIN Customer ON CustName GROUP BY CustName WHERE LotNum = TextBox1.Text.

Or something similar...

 

[Moordoom]

This is what I worked out so far.

SELECT LotNum from Shipper INNER JOIN CustOrder ON Shipper.CustOrderNum = CustOrder.CustOrderNum INNER JOIN Customer ON CustOrder.CustNum = Customer.CustNum ORDER BY Customer.CustName

But is is displaying the LotNum and not the CustName.

 

[Moordoom]

My latest rendition...

SELECT c2.CustName from Customer as c2
INNER JOIN CustOrder as c1 ON (c2.CustNum = c1.CustNum)
INNER JOIN shipper as s1 on (c1.CustOrderNum = s1.CustOrderNum)
WHERE lot = Textbox1.TEXT

I am thinking that may work.

 

[Herman]

Close, but no cookie...

SELECT c.CustName
FROM Shipper s
INNER JOIN CustOrder co ON s.CustOrderNum = co.CustOrderNum
INNER JOIN Customer c ON co.CustNum = c.CustNum
WHERE s.LotNum = @MyLotNumVariable