LEFT JOIN issue in MySQL

[nozom]

I have a table 'people' and a table 'usergroups'

this works fine
$sql=mysql_query("SELECT * FROM people
LEFT JOIN people.P_ID=usergroups.P_ID
");

but if i put
$sql=mysql_query("SELECT * FROM people
LEFT JOIN people.P_ID=usergroups.P_ID
WHERE usergroups.Group_ID LIKE '%'
");
where the % has come from a form.
It doesn't show all results because not every person has an entry in usergroups, even though we are checking for ANY results.

is there a way to do this in the query where it will always list everyone in people, even though they have not got an entry in usergroups.

Make sense??
Nozom

[tsinka]

Hi,

this might work:

$sql = <<< EOSQL
SELECT * FROM people LEFT JOIN usergroups
ON people.P_ID=usergroups.P_ID
WHERE usergroups.P_ID>0 OR usergroups.P_ID IS NULL
EOSQL;

This fetches just all records.

Didn't try this one but it might work also:

$sql = <<< EOSQL
SELECT * FROM people LEFT JOIN usergroups
ON people.P_ID=usergroups.P_ID
WHERE usergroups.Group_ID LIKE '%' OR usergroups.GROUP_ID IS NULL

So if you want to check for any Group_ID including people that don't have any record in usergroups you add the OR part.

Thomas

[nozom]

$sql = <<< EOSQL 
SELECT * FROM people LEFT JOIN usergroups 
ON people.P_ID=usergroups.P_ID 
WHERE usergroups.Group_ID LIKE '%' OR usergroups.GROUP_ID IS NULL

That works for when the $POST[var]='%' but if
$POST[var]=3 it will return the NULL as well

The way i'm doing this at the mo is

if ($_POST[var]!='%')
{
$sqlvar="usergroups.Group_ID LIKE '$_POST[var]' ";
}
$sql = mysql_query("SELECT * FROM people LEFT JOIN usergroups 
ON people.P_ID=usergroups.P_ID 
WHERE  $sqlvar ");

Is there any other way round this?

Ta,
nozom