MySQL joining - a tricky problem from a newby

Question

MySQL joining - a tricky problem from a newby

dkreynolds

Valued Member

Points: 65
More actions
October 27, 2024 at 7:29 pm
Go to Answer
#4474283

Hi,
I'm not even sure whether this is possible. I've three tables, one containing a location ID, and a location name. The second table is a users table, which contains the kind of information you'd expect, i.e. name address etc. The third table is a transactions table containing rows describing transactions which can be described in the following way.
A client requests to be driven somewhere. A driver volunteers for this job. The table contains the date of the trip, plus a user id for the client a user id for the place/location, and a user id for the driver.
Drivers and clients are both in the users table.
Ive set up foreign keys in the transactions table for the place id, and for the user id.
I now need to select and display the information, i.e.
Client name, driver name, place name. I'm not completely ignorant on this subject, and have used MySQL in the past, but not in this way.
1.Is this possible, given that the users file contains both clients and drivers. If not, just break the news, and I'll need to find another way.
2.If it is possible, can anyone give guidance as to the correct syntax for such a MySQL statement. There are other columns I need, but keeping it simple, how do I get:
Client name, Place name, and driver name. That should get me started.
Thanks.
Jeff Moden

SSC Guru

Points: 1004406
More actions
October 30, 2024 at 12:08 am
Answer
#4475180
Try this... notice the only change is a couple of left outer join's for the drivers table (the word OUTER is optional... I left it out). I not only did these on the join of the Drivers table but also on the Places table, just to cover all eventualities.
```
SELECT requests.r_id AS request_id,
requests.r_placeid as requests_pid,
requests.r_aptdate,
client.u_id AS client_id,
client.u_fname AS client_fname,
client.u_sname AS client_sname,
client.u_addr1 AS client_addr1,
client.u_postcode AS client_postcode,
driver.u_id AS driver_id,
driver.u_fname AS driver_fname,
driver.u_sname AS driver_sname,
places.p_name AS p_name
FROM requests
JOIN users AS client ON requests.r_userid = client.u_id
LEFT JOIN places ON requests.r_placeid = places.p_id
LEFT JOIN users AS driver ON requests.r_driverid = driver.u_id;
```
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Put the same table in the FROM clause twice using a JOIN. The key will be to give each instance of the table a different alias... one called "Driver" and one called "Client" with different criteria for both and use "2 part naming" of aliasname.columnname for each column reference.

I've never programmed in MySQL but it should be possible to do this in MySQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dkreynolds Valued Member Points: 65 More actions · Answer 2

Thanks for this. It confirms my googling explroations, and I'm about to try it.

I wonder what will happen on those requests where a driver hasn't yet been allocated. They still need to show in this current repoert.

I'll give it a try.

Thanks.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

dkreynolds wrote:

Thanks for this. It confirms my googling explroations, and I'm about to try it.
I wonder what will happen on those requests where a driver hasn't yet been allocated. They still need to show in this current repoert.
I'll give it a try.
Thanks.

You could do an "OUTER JOIN" (Either Left, Right, or Full depending) so that a Client without a Driver (or vice versa or both) will be reported as a problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dkreynolds Valued Member Points: 65 More actions · Answer 4

Hmm, this is what I have, and whilst it doesn't crash with any syntax errors, it produces nothing. Currently, the requests table has no drivers, but I still need it to work, i.e. produce results, if necessary, with the driver name null.

Here is my code, and I apologize in advance for not formatting it correctly. I'm using a screen reader, and getting it in the correct format is causing problems.

SELECT requests.r_id AS request_id,

requests.r_placeid as requests_pid,

requests.r_aptdate,

client.u_id AS client_id,

client.u_fname AS client_fname,

client.u_sname AS client_sname,

client.u_addr1 AS client_addr1,

client.u_postcode AS client_postcode,

driver.u_id AS driver_id,

driver.u_fname AS driver_fname,

driver.u_sname AS driver_sname,

places.p_name AS p_name

FROM requests

JOIN users AS client ON requests.r_userid = client.u_id

JOIN places ON requests.r_placeid = places.p_id

JOIN users AS driver ON requests.r_driverid = driver.u_id;

dkreynolds Valued Member Points: 65 More actions · Answer 5

Jeff, This works perfectly. The LEFT JOIN did the trick. I'm investigating this as part of my quest for knowledge. Thanks!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

dkreynolds wrote:

Jeff, This works perfectly. The LEFT JOIN did the trick. I'm investigating this as part of my quest for knowledge. Thanks!

Thank you for the feedback!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)