MySql选择JOIN问题。 只是不知道该怎么做

I have 2 tabels (shown in the pictures) the first is a pages table (cvl_pages) the second is the mysettings table.

On the my settings table you will see columns like: fav1, fav2, fav3, fav4, fav5, fav6 -- the integers in these fields correspond to the Page_ID found on the cvl_pages table.

I am trying to write a select statement that will select Page_ID, Page_Title, function from the cvl_pages table WHERE PAGE_ID = fav 1 thru 6.

I then need to create variables for the page title/function that corresponds to fav1, fav2, fav3... etc.

I am having a hard time putting it together in my head...without creating a separate SELECT statement for each "fav"

I guess I am asking for some help creating just one select statement that will give me the correct page title and function for each of the fav fields.

thanks!!!!

You will have to join each different fav, but you can do that in one query:

select 
  s.*, /* or more specific fields you need from s */
  f1.Page_Title as Page_Title1,
  f1.function as function1,
  f2.Page_Title as Page_Title2,
  f2.function as function2,
  f3.Page_Title as Page_Title3,
  f3.function as function3,
  f4.Page_Title as Page_Title4,
  f4.function as function4,
  f5.Page_Title as Page_Title5,
  f5.function as function5,
  f6.Page_Title as Page_Title6,
  f6.function as function6
from 
  mysettings s
  inner join cvl_pages f1 on f1.Page_Id = s.fav1
  inner join cvl_pages f2 on f2.Page_Id = s.fav2
  inner join cvl_pages f3 on f3.Page_Id = s.fav3
  inner join cvl_pages f4 on f4.Page_Id = s.fav4
  inner join cvl_pages f5 on f5.Page_Id = s.fav5
  inner join cvl_pages f6 on f6.Page_Id = s.fav6

Why can't you do a simple join? something like:

SELECT Page_ID, Page_Title FROM cvl_pages p join mysettings s 
    on p.Page_ID in (s.fav1, s.fav2, s.fav3, s.fav4, s.fav5, s.fav6)
    where s.User_ID = 43;

Right in that case, you should use GolezTrol's answer, but use LEFT JOIN instead of Inner Join:

SELECT settings.User_ID, 
  f1.Page_ID as Page_ID1, f1.Page_Title as Page_Title1,
  f2.Page_ID as Page_ID2, f2.Page_Title as Page_Title2,
  f3.Page_ID as Page_ID3, f3.Page_Title as Page_Title3,
  f4.Page_ID as Page_ID4, f4.Page_Title as Page_Title4,
  f5.Page_ID as Page_ID5, f5.Page_Title as Page_Title5,
  f6.Page_ID as Page_ID6, f6.Page_Title as Page_Title6,
  FROM mysettings s 
    left join cvl_pages f1 on s.fav1 = f1.Page_ID
    left join cvl_pages f2 on s.fav2 = f2.Page_ID
    left join cvl_pages f3 on s.fav3 = f3.Page_ID
    left join cvl_pages f4 on s.fav4 = f4.Page_ID
    left join cvl_pages f5 on s.fav5 = f5.Page_ID
    left join cvl_pages f6 on s.fav6 = f6.Page_ID
  where s.User_ID = 43;