JAVA mySQL JOIN 2个表

Question

嘿，大家好，我正在寻找从 2 个表中获取信息，以使用该信息创建一个 JTABLE。

我查看的表格是“发货量”和“客户”

发货采取以下形式

shipNumber | shipperID | destID | size | weight

客户采取以下形式

ID | lastName | firstName | street | city | state | zip

shipperID 和 destID 均指客户 ID。

我正在尝试从客户表中获取与 ShipperID 和 destID 相对应的城市/州信息。

我尝试过以下方法

query =  "SELECT shipments.shipNumber, customers.city, customers.state, customers.city, customers.state FROM shipments, customers WHERE shipments.shipperID = customers.ID";

意识到重复的customers.city/customers.state 两次填充相同的信息。如前所述，我正在尝试获取托运人城市/州和目的地城市/州。

我也尝试过

 query           = "SELECT shipments.shipNumber, customers.city, customers.state, customers.city, customers.state, shipments.size"
                 +         " FROM shipments"
                 +         " INNER JOIN customers ON customers.id = shipments.shipperID";

这提供了相同的信息。

我不确定如何引用 destID = customer.id

谢谢，迈克

Answer 1

通常的技巧是与 customers 表连接两次，一次用于发货人，一次用于目的地:

SELECT     shipments.shipNumber, 
           shipper.city, shipper.state, 
           dest.city, dest.state, 
           shipments.size
FROM       shipments
INNER JOIN customers shipper ON shipper.id = shipments.shipperID
INNER JOIN customers dest    ON dest.id = shipments.destID