do i need a JOIN?

[sneakyimp]

OK

the concept of a join kinda mystifies me. i currently have this query:

$sql = "SELECT name, email, phone, access_level, confirmed, activated FROM " . 
		DB_USER_TABLE . " as t1, " . 
		DB_REGISTRATIONS_TABLE . " as t2 " . 
		"WHERE t1.username = t2.email" . 
		" ORDER BY $sortby$desc " .
		" LIMIT $offset, $users_per_page;";

problem is, there are some items in the USER table that get left out by this query because there is no corresponding record in the REGISTRATIONS table.

can someone suggest a way to join up two tables on a field but also retrieve elements from one table where there is no corresponding value in the other table?

[nemonoman]

What you're describing is a LEFT JOIN or outer join. MySQL calls it a LEFT JOIN

something like:
SELECT name, email, phone, access_level, confirmed, activated FROM " .

DB_USER_TABLE . " as t1, "

LEFT JOIN

DB_REGISTRATIONS_TABLE . " as t2 " .

ON
t1.username = t2.email" .

" ORDER BY $sortby$desc " .
" LIMIT $offset, $users_per_page;";

You'll have to fix up the quotes and so on, but that should get you going.

[sneakyimp]

thanks so much! i have it working now.

do you know of any resources online where i could learn more about joins (left vs right vs straight vs inner vs outer, etc.)?

i need to know more about this stuff.

[nemonoman]

Google SQL tutorial, or look in MySQL articles section.

A basic JOIN, also called an INNER JOIN or RIGHT JOIN connects 2 tables where column values are =

SELECT *
FROM table1, table
WHERE table1.name=table2.name <<implicit join

OR

SELECT *
FROM table1
INNER JOIN table2 ON table1.name=table2.name

Both statements produce the same results.

Typically you'll use inner joins, because for the most part you are looking for records with an implicit relationship:

SELECT track.name FROM cdtrack, cd
WHERE cdtrack.albumname=cd.albumname

Sometimes you want to find records where there is no implicit relationship

SELECT cd.name producer.info FROM producer, cd
WHERE cd.producer=producer.name

This would only show rows where there cd producer and producer name was the same.

To see all cd names, and whatever related producer names you might have around, LEFT JOIN:

SELECT cd.name producer.info FROM cd
LEFT JOIN producer ON cd.producer=producer.name

on the Imaginary LEFT would be all the CDs including those with no producer matches.
on the Imaginary Right would be any producer names that matched up.

OR

SELECT cd.name producer.info FROM producer
LEFT JOIN cd ON cd.producer=producer.name

The above would show all the PRODUCERS, including those with no cd matches. on the Imaginary LEFT would be all the Producers including those with no cd matches.
on the Imaginary Right would be any cd names that matched up.

It takes some head bending, but once your brain is totally and completely messed up, it all makes sense!

Basically:
normal (INNER) join when you're SURE there will be a related record in all cases:
(SELECT town.name, state.name FROM town, state WHERE town.stateid=state.id)

Left (or OUTER) join when there records have a relationship, not necessarily one for one:

SELECT person.name, college.name FROM person, college
WHERE person.graduateschoolid=college.id

Would show only rows where person went to grad school.

SELECT person.name, college.name FROM person LEFT JOIN college on person.graduateschoolid=college.id

Would show ALL persons, with college information if the person went to grad school.

Good luck:

[nemonoman]

I should mention that there is virtually no end to the number of Joins you can create in a single query. You can even join tables to themselves.