CODEWITHSUNDEEP
mysqlselectreferencesubquery
Delliardo
Delliardo

Reputation: 185

MySQL referencing outer select in subquery

I have two tables, shipments and customers. I want to print out the customers' last names with the shipments. Tables are set up as:

shipments:  shipNumber, shipperID, destID, size, weight

shipperID and destID are foreign keys from customers table.

customers:  id, lastName, street, city, state, zip.

What I want when I query is a table like this:

shipNumber, shipperID(customers.id), customer.lastName, destID(customers.id), customer.lastName

shipperID and destID are not the same, therefore the customers.lastName fields should only relate to the id field they come after.

My current query that doesn't work for obvious reasons:

SELECT shipments.shipNumber, shipments.shipperID, customers.lastName, shipments.destID, 
(SELECT customers.lastName 
FROM shipments, customers 
WHERE shipments.destID = customers.id)

FROM shipments, customers
WHERE shipments.shipperID = customers.id;

My subquery will obviously print more than one row, but I want the last name of the customer referenced by destID in relation to the shipnumber, but I'm aware that mySQL looks at queries inside out.

Sample Data:

shipments table
+------------+-----------+---------+------+--------+
| shipNumber | shipperID | destID  | size | weight |
+------------+-----------+---------+------+--------+
| S12345-0   | W-100     | C-22315 |   12 |     12 | 
| SYY89-4    | C-15572   | U-01002 |   12 |     12 | 
+------------+-----------+---------+------+--------+

customers table
+---------+------------------+
| id      | lastName         |
+---------+------------------+
| S-01210 | Home Depot       | 
| S-18537 | Honda            | 
| S-13349 | NCR              | 
| C-15572 | GM               | 
| C-00122 | Delco            | 
| W-100   | Warehouse A      | 
| U-01002 | Wright State     | 
| W-210   | Warehouse B      | 
| U-00013 | AFIT             | 
| C-22315 | Northrop Grumman | 
+---------+------------------+

What I want the query to show:

shipNumber    shipperID    lastName     destID   lastName
S12345-0      W-100        Warehouse A  C-22315  Northrop Grumman

This was data provided to me, so the lastName column being filled with data that are not last names is probably just an oversight, so just go with it.

Upvotes: 0

Views: 80

Answers (1)

John Ruddell
John Ruddell

Reputation: 25862

If i understand your question correctly, you don't need a subquery, you just need to join the two tables together. the customers needs to be joined twice.. once for shipperID and once for destID to get the corresponding lastName. this query should do the trick.. see working FIDDLE

SELECT 
    s.shipNumber, 
    s.shipperID, 
    c.lastName AS first_last_name, 
    s.destID, 
    c1.lastName AS second_last_name
FROM shipments s
JOIN customers c ON c.id = s.shipperID
JOIN customers c1 ON c1.id = s.destID

Upvotes: 1

Related Questions