Left Join Only Returns If Matching

Question

I have two tables (claim_pics & claiminfo) I am doing a query for a results page.

Its works well.. when there there is a match of the two tables claim_id ( claiminfo.claim_id = claim_pics.claim_id ).

However when there there is only one table with a claim_id, namely in claiminfo, and not a matching claim_id, all of the attributes of the row show up except the claim_id.

There is no claim_id, so if a user clicks an edit button, there is no claim_id to tell it where to go.

I need it present the $results of the claim_id regardless is there is a match or not. Any ideas or work arounds?

Thanks.

$query   = "SELECT * FROM claiminfo 
                          LEFT JOIN ( claim_pics ) 
                          ON ( claiminfo.claim_id = claim_pics.claim_id )  
            WHERE( userid='$userid' )   
            ORDER BY claiminfo.ts
            DESC LIMIT 200";

// Will only return

claim_ids that both pics and info match

                  $result = mysql_query( $query );

                  while( $row = mysql_fetch_assoc( $result ) ) { 


                                    $namelocation =      $row['namelocation'];
                                    $claimholder =   $row['claimholder'];
                                    $occasion =      $row['occasion'];
                                    $geotag =    $row['geotag'];
                                    $lat =   $row['lat'] ;
                                    $lng =   $row['lng'];
                                    $claim_id = $row['claim_id']; 
                                    $img = $row['location'];




                                     $html = " ... "

PLEASE DON'T SHOUT. The shift key exists because proper casing makes text easier to read, and SHOUTING is annoying. — Ken White, Oct 16 '12 at 01:26
I think this is because the * is picking up the clam_id of claim_pics table instead of your claiminfo table. — You Qi, Oct 16 '12 at 01:29
My bet is that your limit clause is showing what seems to be only `claiminfo.claim_id = claim_pics.claim_id` - try returning all the rows and see if you get NULLs on the right hand side — Adrian Cornish, Oct 16 '12 at 02:14

score 0 · Accepted Answer · edited May 23 '17 at 11:56

0

As comments have noted, it is likely that a null claim_id from the empty claim_pics row is blanking the $row['claim_id']. The easiest way to get around this is to explicitly select and alias the claiminfo.claim_id:

$query   = "SELECT *, claiminfo.claim_id AS id FROM claiminfo 
                          LEFT JOIN ( claim_pics ) 
                          ON ( claiminfo.claim_id = claim_pics.claim_id )  
            WHERE( userid='$userid' )   
            ORDER BY claiminfo.ts
            DESC LIMIT 200";

You can then reliably get the claim_id with the variable $row['id'].

In a more general note, this situation demonstrates why it is generally better not to use SELECT *.

edited May 23 '17 at 11:56

Community

1
1

answered Oct 16 '12 at 02:56

John C

8,223
2
36
47

Tried it, it didn't return anything. Is the syntax correct for multi-select, meaning the comma? – Juan Carlos Chavez Oct 16 '12 at 03:17
Oops, have to do the asterisk first when specifying rows. Fixed my answer. – John C Oct 16 '12 at 04:03

Left Join Only Returns If Matching

1 Answers1