Query help, giving me more results than exist.

[coupe-r]

Hi All,

 

I'm joining my passes table with my property table so when I'm on the passes page, I can see which properties are assigned to the list of passes.  However, I have 7 pass records (101 - 107), but my query pulls 15 records (103 - 107) 3 times...

 

Now if I delete a property record, it then pulls those records 2 times or if I add a 4th, it pulls them 4 times each..

 

Here is the query:

 

$sql	 = "SELECT p.pass_id, p.property_id, p.number, p.descrip, pr.address1, pr.address2 ";
$sql	.= "FROM passes p LEFT JOIN property pr ON p.client_id = pr.client_id ";
$sql	.= "WHERE p.client_id = '".$_SESSION['client_id']."' AND pr.client_id = '".$_SESSION['client_id']."' AND p.deleted_on IS NULL ";
$sql	.= "ORDER BY p.pass_id DESC LIMIT $offset, $rowsperpage";

[AyKay47]

from your query, i can't see why it would be giving you multiple results.. can you post the relevant code

[coupe-r]

Thanks for reading, however, the relevant code shouldnt matter.

 

This query pulls 7 records, which is correct.

SELECT COUNT(pass_id) FROM passes WHERE client_id = '".$_SESSION['client_id']."' AND deleted_on IS NULL

 

As soon as I join it with the property table, it grabs multiple, because there are multiple properties.  Each table has client_id, so I'm assuming its finding a property and listing the passes for each instance?

[AyKay47]

the WHERE clause will determine how many rows are actually grabbed.. the ON clause will only determine how the tables are joined.. that being said.. try this

 

$sql	 = "SELECT p.pass_id, p.property_id, p.number, p.descrip, pr.address1, pr.address2 ";
$sql	.= "FROM passes p LEFT JOIN property pr ON p.client_id = pr.client_id ";
$sql	.= "WHERE p.client_id = '".$_SESSION['client_id']."' AND p.deleted_on IS NULL ";
$sql	.= "ORDER BY p.pass_id DESC LIMIT $offset, $rowsperpage";

[coupe-r]

Ahh, you got it, sorta...

 

Removing pr.client_id from the WHERE clause and changing what the tables are joined on fixed it.  client_id = client_id should have been property_id = property_id..

 

$sql	 = "SELECT p.pass_id, p.property_id, p.number, p.descrip, pr.address1, pr.address2 ";
$sql	.= "FROM passes p LEFT JOIN property pr ON p.property_id = pr.property_id ";
$sql	.= "WHERE p.client_id = '".$_SESSION['client_id']."' AND p.deleted_on IS NULL ";
$sql	.= "ORDER BY p.pass_id DESC LIMIT $offset, $rowsperpage";

 

Thanks so much