CODEWITHSUNDEEP
javamysqlsqljdbc
fisherml
fisherml

Reputation: 65

JAVA mySQL JOIN 2 tables

Hey guys I'm looking to get information out of 2 tables to create a JTABLE with that information.

The tables I am look at are 'shipments' and 'customers'

Where shipments takes the form of

shipNumber | shipperID | destID | size | weight

and customers takes the form of

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

The shipperID and destID both refer to a customer ID.

I am trying to get the city/state information out of the customers table that corresponds to the shipperID and destID.

I have tried the following

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

Realizing that the duplicate customers.city/customers.state is populating the same information twice. As previously said, I am trying to get the shipper city/state and destination city/state.

I also tried

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

Where this gives the same information.

I am not sure how to reference the destID = customer.id

Thanks, Mike

Upvotes: 0

Views: 6130

Answers (1)

Mureinik
Mureinik

Reputation: 311188

The usual trick is to join with the customers table twice, once for the shipper and once for the destination:

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

Upvotes: 2

Related Questions