SQL Join Question

i3bargon

I am having trouble creating a select query that contains a join and a NOT. Any help would be greatley appreciated.

I have a table A consisting of
person_id, name

I have a table C consisting of
person_id, group_id

I want to get every A.person_id that doesnt exist in table C and C.group id cant be equal to 3

For Example
Table A
person_id name
1 dog
2 cat
3 cow
4 goat
5 mouse
6 deer

Table C ---
person_id group_id
1 3
4 4
4 2
4 3
1 7
15 3

The result im trying to get would be from table A
2 cat
3 cow
5 mouse
6 deer

Thanks in advance.

Lars_Berg

select * from a left outer join c
on a.person_id = c.person_id
and c.group_id <> 3
where c.person_id is null

i3bargon

I tried that solutino and it didnt work. Any other suggestions? What im trying to get is each row in Table A that doesnt have its person_id and a group_id of three. So if there is a combination of a (A.person_id, group_id=3) in table C then i dont want to display that (A.person_id, A.name) from A.

This psuedo code might help explain what im trying to do.

A.row = 0;
C.row = 0;

Grab the row from table A
{
   while(still rows in C)
   {
      If(C.person_id == A.person_id && C.group_id == 3)
      {
         break while loop and goto next row in A
      }
      else
      {
          C.row++
      }
    }
     We went through table c and didnt find the match so we
     output A.person id and A.person_name.

A.row++;
}

TTT

if you still have problems with that try to do it other way:

$q1 = select person_id from a

while(foo = mysql_fetch_row($q1))
{
$the_list = $the_list.$foo[0].",";
}

$the_list = substr($the_list,0,strlen($the_list)-1);

$q2 = select * from c where group_id <> AND person_id NOT IN ($the_list)

i think outer join goes

select cTable.person as id
from cTable c left outer join
aTable a ON c.person_id =a.person_id
where c.group_id <> 3 and c.person_id is NOT null

i3bargon

I obtained the result I wanted by the following code. I know this is a lot moe inefficent than using a join. It works quickly now, but my table will get huge later on and speed is important. Any suggestions on a Join that will obtain the desired result


$sql = "SELECT * FROM A";
$sql2 = "SELECT person_id FROM C WHERE group_id = 3";
$result = mysql_query($sql,$dbConn);
$result2 = mysql_query($sql2,$dbConn);

$counter = 0;

// Figure out which people  are closed
while($obj2 = mysql_fetch_object($result2))
{
   $closed_people[$counter] = $obj2->person_id;
   $counter++;		
}

// Output only the people that arent closed and are in group 3 
while($obj = mysql_fetch_object($result))
{
   if(!in_array($obj->person_id,$closed_people))
   {
       echo $obj->person_id;

}
}

Lars_Berg

I tested my query with the sample data you provided and it gives the desired result.

In what sense did it not work for you?

dzekic

Originally posted by Lars Berg

select * from a left outer join c
on a.person_id = c.person_id
and c.group_id <> 3
where c.person_id is null

[/B]

Anyway I don't understand use of "where c.person_id is null" here... If someone can I explain I would be gratefull.

Lars_Berg

A left outer join means that all rows in the left table (A in this case) will be returned regardless of if there is a matching row in C

Given the following data

A_ID

1
2
3

C_ID

3
4

then

select * from A left outer join C on A_ID = C_ID

would give the result

A_ID C_ID

1......<null>
2......<null>
3......3

thus the predicate

where C_ID is null

would filter the rows where there was no matching records.

Normally this type of query would be expressed as

select * from a where a_id not in (select c_id from c)

Not supported in Mysql though.