View Full Version : Only where a record exists

01-26-2012, 11:07 AM
Is it possible to create a query where the results only match the records in another database?

Here is my example:

I have 2 tables:


Sometimes there are spurious records in the dispatches database as new records are imported but it is important that I only pull the dispatches for my contracted partners (for a dashboard and trending).

So, I need to pull records from the 'dispatches' table ONLY where it is a valid partner in the partner table with doing multiple select clauses.

Any help would be appreciated...

Thanks in advance

01-26-2012, 02:30 PM
you would do an INNER JOIN on the tables. you'd have to determine your columns to join on since you've not provided your table layouts.

01-26-2012, 03:55 PM
I'm so close here...

any clue what is wrong with my code?

include '../config.php'; //connect to the database

$sql = "SELECT partner_name FROM partners";//contains only the contracted partners

$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$partners = mysql_fetch_array($result);//assign to an array

$sql = "SELECT * FROM dispatches WHERE Dispatched_TPM IN('".implode("', '", $partners)."')"; //fetch only dispatches where partner is contracted

$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
while( $row = mysql_fetch_assoc( $result ) ) {
echo $row['Dispatched_TPM'];
echo "<br>";

If I type out the $partners array instead of pulling from MySQl it works. When I add the SELECT clause to create the partners array all I get is the first partner... must be close.

01-26-2012, 07:45 PM
Follow what guelphdad suggests with a simple join:

SELECT * FROM dispatches INNER JOIN partners ON partner_name = Dispatched_TPM

01-27-2012, 06:54 AM
How did I miss that post? So sorry... thanks for the advice... works great