CODEWITHSUNDEEP
mysqljoin
Symbal
Symbal

Reputation: 370

mySQL, multiple joins on same table with different criteria. Rename columns at query

Greetings and thank you for reading my question.

First up, the graphic below depicts part of a database I am creating. My goal (unless there is any better way to do this) is to support multiple languages for fields which would normally be called something like "schedule_name" and "schedule_description" in to a seperate table. P.S - I know the column types in this diagram are labelled badly.

database

As you can see, boat, cruise and schedule each have a languageid as foreign key. If I want to pull out the schedule record with cruise and boat, how would I go about this please?

I have the tables joined no problem, column alias' sorted but, would like to get the correct details in to each alias. For clarity

Schedule has it's own language_language_id reference to pick out using language_id = 1 language_name_en is - Monday Morning

Cruise has it's own language_language_id reference to pick out using language_id = 2 language_name_en is - Sunny Haze

Boat has it's own language_language_id reference to pick out using language_id = 3 language_name_en is - Blue Dolphin

Answering a comment to this question; en is for english, zhs for simplfied chinese and zht for traditional Chinese. These are the languages this system is to support for dynamic data. I thought it would be convenient to lay out the tables like this so there can be a user type who is only responsible for dealing with language; no need to deal with the scheduling table.

So far, I believe my query would go as follows to attain a sample record as shown afterwards:

SELECT 
   schedule_id,
   s.language_name_en as schedule_name_en,
   l.language_name_zhs as schedule_name_zhs,
   l.language_name_zht as schedule_name_zht,
   schedulesafename,
   schedule_expected_arrival,
   schedule_expected_departure,
   cruise_id,
   c.language_name_en as cruise_name_en,
   c.language_name_zhs as cruise_name_zhs,
   c.language_name_zht as cruise_name_zht 
   FROM
      schedule as s 
   INNER JOIN
      language as l 
         ON s.language_language_id = l.language_id
   INNER JOIN
      cruise as c
         ON c.language_language_id = l.language_id
   Where
      schedule_id = 1;

Here is some sample data for what I want returned using the query listed.

scheduleid - 1
schedule_name_en - monday morning
schedule_name_zhs - 星期一的早晨
schedule_name_zht - 星期一的早晨
schedule_safename - pwupglfkpmwcbkgzhmzxrqfeqzlhvaed 
schedule_expected_arrival - 1353138308
schedule_expected_departure - 1353139218
cruise_id - 1 
cruise_name_en - Sunny Haze
cruise_name_zhs - 彩霞
cruise_name_zht - 彩霞
cruise_safename - bbhdrunzdmftyvhprefvogysgfrtnkgm

This gives you an idea of how I would want to grab the data from the database. My main problem being how to rip out the language records in the same query. Thank you kindly for reading my question.

Upvotes: 1

Views: 7364

Answers (1)

DRapp
DRapp

Reputation: 48139

I think you are very close, you just need to re-use the same language table with different alias for the respective table it is going for... something like

SELECT 
      S.schedule_id,
      SL.language_name_en as schedule_name_en,
      SL.language_name_zhs as schedule_name_zhs,
      SL.language_name_zht as schedule_name_zht,
      schedulesafename,
      S.schedule_expected_arrival,
      S.schedule_expected_departure,
      C.cruise_id,
      CL.language_name_en as cruise_name_en,
      CL.language_name_zhs as cruise_name_zhs,
      CL.language_name_zht as cruise_name_zht 
   FROM
      schedule as s 
         INNER JOIN language as SL 
            ON s.language_language_id = SL.language_id
         INNER JOIN cruise as c
            on s.Cruise_Cruise_ID = c.Cruise_Id
            INNER JOIN language as CL 
               ON c.language_language_id = CL.language_id
   Where
      S.schedule_id = 1;

Notice first, the Schedule (alias S) joined to language (first time is aliased as "SL" for Schedule Language ) on the schedules language ID join.

Next, the schedule joins to the CRUISE on the Cruise ID.

Finally, the CRUISE table is joined to the language table (this time is aliased "CL" for the Cruise Language ) by the CRUISE language ID value.

So you now have the same table source twice, each for the respective "Language ID" elements you are trying to pull out.

Upvotes: 4

Related Questions