Page 1 of 1

joins

Posted: Tue May 31, 2005 8:29 am
by shiznatix
can someone explain to me exactally what join does? in dont understand exactly how to use it and i dont understand the diffrences between inner join, left join, and right join (and are there more?). i am creating a search engine that has to be quite advanced and i talked to a friend who showed me some good ways of doing it. heres what i have planned but im not real sure i understand whats going on.

Code: Select all

table query_from -

(1) join mdl_cv_personal_info as personal_info //i see it creates a nick name for it called personal_info
(2) join mdl_cv_personal_info_lang as personal_info_lang
//thats it for the beginning of the 1st table

table query_select - 

title++++++|++++++++field_name++++++++|+query_from_id+|
first_name | personal_info.first_name | 1 
last_name  | personal_info.last_name  | 1
information| personal_info_lang.info  | 2
//the first_name is queryed from the join in the 1st table etc...

table query_where -

//really not clear on how to do this but maybe somthing like
(1) first_name (=, !=, <, >, like...pick one of these somehow) personal_info.first_name
as you can probebly tell i am a bit lost and i cant read the mysql documentation cause that makes even less sence to me. any help would be nice. thanks

Posted: Tue May 31, 2005 12:39 pm
by JAM

Posted: Tue May 31, 2005 3:18 pm
by shiznatix
ok obviously i dont understand. im trying this query and it is giving me a error

Code: Select all

$query = '
    SELECT
        *
    FROM
        mdl_usr_users
        LEFT JOIN ON mdl_cv_personal_info
    WHERE
        mdl_user_users.user_id = mdl_cv_personal_info.fk_user_id
';
whats wrong? (i also tried taking out the ON but that made no diffrence)

Posted: Tue May 31, 2005 5:38 pm
by timvw
You forgot to specify the second table you want to join with...

For example:

Code: Select all

SELECT *
FROM mdl_cv_personal_info AS info
LEFT JOIN &#1111;b]cv_personal_info_lang&#1111;/b]  AS lang ON info.whatevercol=lang.othercol
A little websearch on SQL / join will provide you a load of good sites that try to explain the subject...

Posted: Tue May 31, 2005 5:49 pm
by shiznatix
ok i figured it out, i had to use AS somting after each table, but why do i have to use AS, why cant i just use the table name?

Posted: Tue May 31, 2005 5:54 pm
by timvw
The error is that you forgot to mention the table2


SELECT *
FROM table1
JOIN table2 ON table1.col=table2.col

The alias was only used to reduce the writing of table1.col=table2.col...