[SOLVED] Query Help

[l!m!t]

Hi - I have an SQL query I am trying to build, but after hours I am just lost on how to do this.  I will try and explain my scenario and also my code.

 

I have 3 tables I want to pull data from to build a pull down menu, in order to do this I have to use joins. One of the tables contains ID's for the menu which I don't want pulled as they are already "used". I cant figure out the SQL query to ignore these ID's. I have tried ! <> NOT EQUAL and even PHP if else, no luck.

 

I am trying to avoid two Query's not sure if its even possible to do this in one query.

 

The table ID's I want it to ignore are in "TABLE_USED" tu.vmobile_id  I have tried v.vmobiles_id != tu.vmobile_id, but doesnt seem to work.

 

Any help or suggestion would be great.

 

$vmobile_pulldown_new = tep_db_query("select v.vmobiles_id, vd.vmobiles_name from " . TABLE_V . " v, " . TABLE_V_DESCRIPTION . " vd, " . TABLE_USED . " vu  where v.vmobiles_id = vd.vmobiles_id and  v.vmobiles_id != tu.vmobile_id and vd.language_id = '" . (int)$languages_id . "' ORDER BY  vd.vmobiles_name");
while ($pulldown_new = tep_db_fetch_array($vmobile_pulldown_new)) {
        $vmobile_array_insert.='<option '.$selected.' value="'.$pulldown_new['vmobiles_id'].'">'.$pulldown_new['vmobiles_name'].'</option>';
}

 

 

[kickstart]

Hi

 

If I have understood what you want, something like this maybe:-

 

SELECT v.vmobiles_id, vd.vmobiles_name 
FROM " . TABLE_V . " v, 
JOIN " . TABLE_V_DESCRIPTION . " vd
ON v.vmobiles_id = vd.vmobiles_id 
LEFT OUTER JOIN " . TABLE_USED . " vu  
ON v.vmobiles_id = vu.vmobile_id 
WHERE vd.language_id = '" . (int)$languages_id . "' 
HAVING vu.vmobile_id  IS NULL
ORDER BY  vd.vmobiles_name

 

Use a left outer join to link in the vu.vmobile_id  but using HAVING to ignore any where there is a matching record on vu.vmobile_id.

 

All the best

 

Keith

[l!m!t]

Hi

 

If I have understood what you want, something like this maybe:-

 

SELECT v.vmobiles_id, vd.vmobiles_name 
FROM " . TABLE_V . " v, 
JOIN " . TABLE_V_DESCRIPTION . " vd
ON v.vmobiles_id = vd.vmobiles_id 
LEFT OUTER JOIN " . TABLE_USED . " vu  
ON v.vmobiles_id = vu.vmobile_id 
WHERE vd.language_id = '" . (int)$languages_id . "' 
HAVING vu.vmobile_id  IS NULL
ORDER BY  vd.vmobiles_name

 

Use a left outer join to link in the vu.vmobile_id  but using HAVING to ignore any where there is a matching record on vu.vmobile_id.

 

All the best

 

Keith

 

Keith,

 

Thank you very much for your time!! Worked perfectly.. I would have never figured it out. Learn something new everyday.

 

Thanks again!