Sql with inner joins

I’m trying to request some info from different tables using inner joins. Here is my query and it works

$dbQuery = "SELECT * FROM triplog LEFT JOIN receipts ON receipts.rec_tripid = triplog.tid GROUP BY tripid ORDER BY tid DESC";

Now later I will change the DB structure abit so the shippers and receivers will not be stored in the triplog table but in its own customers table. I will still need to make a list of all the trips and use inner join to get the name of shippers and receivers, but here is where my problem is.

I need help to figure out how to get both shippers and receivers when they are both stored in the customers table.

Here is what I tried to do.

$dbQuery = "SELECT * FROM triplog INNER JOIN customers ON customers.cust_id = triplog.shipper LEFT JOIN receipts ON receipts.rec_tripid = triplog.tid GROUP BY tripid ORDER BY tid DESC";

When printing the triplog list now they shipper comes up with the name instead of the ID, but not the receiver. So I guess I need multiple queries in this one. How can I do that ?

Thanks for reading!

You’ll need to add a second join. I would also strongly suggest you change your query to NOT use SELECT *. You will run into issues with multiple columns with the same column names.

So you’ll need to use something like this (without access to your table structure or what you’re putting on the triplog list, this is a sample)

SELECT t.tripid
     , s.name
     , s.address
     , s.city
     , s.state
     , s.zipcode
     , rcv.name
     , rcv.address
     , rcv.city
     , rcv.state
     , rcv.zipcode
     , t.dateset
     , t.datedelivered
     , t.signedfor
  FROM triplog t
 INNER JOIN customers s ON s.cust_id = t.shipper
 INNER JOIN customers rcv ON rcv.cust_id = t.receiver
  LEFT JOIN receipts r ON r.rec_tripid = t.tid
 ORDER BY tid DESC
1 Like

you are well on the right track, dave

two things – you forgot the t alias, and your two inner joins both incorrectly use t.shipper

I have no idea what you’re talking about :shifty:

Fixed

Thanks for the help. It works perfectly now :smiley:

		$dbQuery = "SELECT t.tripid
					 , s.cust_customer AS shipper
					 , s.cust_address AS sAddress
					 , s.cust_city AS sCity
					 , s.cust_state AS sState
					 , s.cust_zip AS sZip
					 , rcv.cust_customer AS receiver
					 , rcv.cust_address AS rAddress
					 , rcv.cust_city AS rCity
					 , rcv.cust_state AS rState
					 , rcv.cust_zip AS rZip
					 , t.tid
					 , t.pickup
					 , r.rec_tripid
					 , rec_reimbursed
				  FROM triplog t
				 INNER JOIN customers s ON s.cust_id = t.shipper
				 INNER JOIN customers rcv ON rcv.cust_id = t.receiver
				  LEFT JOIN receipts r ON r.rec_tripid = t.tid
				 ORDER BY tid DESC";
1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.