Jump to content
Gilbert

mysql view left join a table with 2 references to it

Recommended Posts

Hi all,  I have a table of a schedule of workers at kiosks and I have a table of drivers who drop them off and possibly a different driver picks them up.  I have a view that lists other details from other tables (like times, dates, names) and I'd like to list the drop off and pickup drivers separately, because they might be different.  I've tried a couple of things and got error messages - like 'referencing same table' when I tried this:

create view allScheduleInfo AS
.....

schedule.dropOffID,
drivers.dropOffName,
schedule.pickUpID,
drivers.pickUpName,
....
FROM schedule
left join .....
left join drivers ON schedule.dropOffID = drivers.driverID
left join drivers ON schedule.pickUpID = drivers.driverID
....

I got it to display the view with an error message when I tried combining the joins on one line, like this -->  left join drivers on schedule.dropOffID = driversID AND schedule.pickUpID = driversID

And the dropOffID and pickUpID go thru correctly, but it makes the dropOffName and the pickUpName = to NULL.    I considered making two separate tables for drop off and pick up drivers, but wouldn't that contradict the normalizing of table data?   I know this may be kind of odd, and i hope I've explained it sufficiently that some one can point me in the right direction of how to do this. 

Basically I guess I am trying to access 2 pieces of information from the same table, but with different pointers to them.     Thank you for your help.

Share this post


Link to post
Share on other sites

Give the table an alias each time and use that to refer to the different fields so that things aren't ambiguous. e.g.:

left join drivers AS d1 ON schedule.dropOffID = d1.driverID
left join drivers AS d2 ON schedule.pickUpID = d2.driverID

Use the same aliases in the select list to make sure you're selecting the correct columns.

  • Like 1

Share this post


Link to post
Share on other sites

Thanks a lot, justsomeguy!  After I posted I tinkered some more and I came up with creating 2 views called viewDropOff and viewPickUp and then referenced them separately in the left joins.   I use aliases sometimes, but I never really understood how powerful they can be, like in this instance.  I will go back and try it the way you said and I'm sure it will work.   Thank you for the help.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...