joins

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

joins

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post 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)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
Post Reply