SQL Join Tables

[eluser]RedIgniter[/eluser]
Hello I have 3 tables in my database and I would like to join them together and get most of the values out of all tables, but not all values, how would I do that? Here is my structure. Like the table users has id's and the other two tables have field uid's which id's of users that inserted stuff. I would want to get users firstname and lastname from users table, get users favorite links and favorite quotes from second table get other stuff from third table and so on.

[eluser]Mischievous[/eluser]

Code:

SELECT user.user_id, user.first_name, user.last_name,
       user_favorites.links, user_favorites.quotes
LEFT JOIN user_favorites USING(user_id)
LEFT JOIN user_third_table USING(user_id)
WHERE user.user_id = 1;

?? with more info could build more specific query

[eluser]RedIgniter[/eluser]
ok here is what my exact table names and stuff.

users table name: "accounts" some fields "firstname, lastname, email" etc. [this table has 'id' as user id]
subject table name: "subjects" some fields "subject, sub_subject, experience" etc. [this table has 'tid' as user id]
days_attending table name: "days_attending" some fields "monday, tuesday, wednesday" etc. [this table has 'tid' as user id]

so i would want the query to select from accounts, also select from subjects with matching ids and select days attending matching id with tid and show them in one row, so I can make a list.

[eluser]Dennis Rasmussen[/eluser]
It's VERY hard to read your setup.
But what makes me wonder... you want all the data in ONE row? :S

[eluser]RedIgniter[/eluser]
I just want a list, where visitors don't see things all over the site but in lists.

[eluser]Unknown[/eluser]
Hi, thanks for providing me this worthy information.SQL join Tables are really tough thing now i have learn about it so many things become clear in my mind.

[eluser]Mischievous[/eluser]

Code:

$query = "SELECT A.firstname, A.lastname, A.email,
       S.subject, S.sub_subject, S.experience,
       DA.monday, DA.tuesday, DA.wednesday
FROM accounts as A
LEFT JOIN subjects as S ON A.id = S.tid
LEFT JOIN days_attending as DA ON A.id = DA.tid";

if($result = $this->db->query($query))
{
   $list = array();
   foreach($result->result() as $row){
      $list[] = $row;
   }

   //For testing, showing your results
   echo "<pre>";
   print_r($list);
   echo "</pre>";
   //End Testings

} else {
echo "problem with query:" . $query;
}

Something like that should work?

[eluser]chandlerou[/eluser]
[quote author="Mischievous" date="1286249857"]

Code:

SELECT user.user_id, user.first_name, user.last_name,
       user_favorites.links, user_favorites.quotes
LEFT JOIN user_favorites USING(user_id)
LEFT JOIN user_third_table USING(user_id)
WHERE user.user_id = 1;

This will return multiple results correct? If the user has more than one 'favorite' and/or 'third_table'?

Is it possible to condense this down to one result? Can SQL dynamically create aliases for every favorite returned? or an array of favorites? all in the same row?

[eluser]chandlerou[/eluser]
/BUMP

Just curious if the above post is possible.

[eluser]WanWizard[/eluser]
No. If this is a one-to-many relationship, you'll get a record for every match.

If you want to condense it, run a foreach loop on the result.