JAVA mySQL JOIN 2 tables

Question

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

It will depend what you want to show in your data: info from the shipper or info from the destined customer. Note that this question is not about Java nor Mysql, is about understanding your requirements. — Luiggi Mendoza
– Luiggi Mendoza, Commented Mar 31, 2014 at 15:31
Why would I need a distinct? I understand what that does, but not sure how this applies — fisherml
– fisherml, Commented Mar 31, 2014 at 15:44

Mureinik · Accepted Answer · 2014-03-31 15:32:06Z

2

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

answered Mar 31, 2014 at 15:32

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fisherml Over a year ago

This worked! I'm not entirely sure of the syntax for 'shipper' 'dest' but could I trouble you for one more sytax related question?

Mureinik Over a year ago

shipper and dest are table aliases - basically, since I'm joining the customers table twice (with a different condition for each one), I need a way to relate to the two different entities. Does this answer your question?

Collectives™ on Stack Overflow

JAVA mySQL JOIN 2 tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related