[SOLVED] NOT IN Query

[iceblox]

Hi All,

 

Im trying to display data from my table called "error" but only if the text in the "model" column is not in the "deals_models" table in the "ModelID" column.

 

This is my query so far.

 

$query = "SELECT * FROM error WHERE affiliate = '$aff' AND model NOT IN (SELECT ModelID FROM deals_models) AND model <> '0' GROUP BY model ORDER BY model DESC";

 

But it only seems to be displaying some of these correct for example. I have "E63 Blue" in the error table but this is not a ModelID in that table and it isnt displaying. However it will display "8800" which isnt in the deals_models table.

 

I think it might be something to do with the structure of the tables. ModelID is set up a "INT" and "model" is set up as "VARCHAR"

 

Could this be the reason why it is not working?

 

Any suggestions would be greatly appreciated.

 

Thanks, Phil

[RussellReal]

SELECT * FROM error JOIN deals_models ON (error.model != deals_models.ModelID) WHERE `affiliate` = '$aff'

[iceblox]

Hi Russell,

 

Thanks for your reply.

 

I tried the code you posted.

 

$query = "SELECT * FROM error JOIN deals_models ON (error.model != deals_models.ModelID) WHERE `affiliate` = '$aff' GROUP BY model";

 

This displays the ones that it didnt before i.e "E63 Blue" but it also displays the ones that are in the models table. Im trying to set it not to display the ones in the deals_models table.

 

Any other suggestions?

 

Thanks for your help

 

Phil

[RussellReal]

is error.model an id number?

[kickstart]

Hi

 

You might well be right with the thought that it doesn't appreciate different data types.

 

If so try this:-

 

$query = "SELECT * 
FROM error 
WHERE affiliate = '$aff' 
AND model NOT IN (SELECT CONVERT(ModelID, CHAR) FROM deals_models) AND model <> '0' GROUP BY model ORDER BY model DESC";

 

You could also try something like:-

 

$query = "SELECT * 
FROM error a
LEFT OUTER JOIN deals_models b 
ON a.model = CONVERT(b.ModelID,CHAR)
WHERE affiliate = '$aff' 
AND model <> '0' 
AND b.ModelID IS NULL
GROUP BY model ORDER BY model DESC";

 

All the best

 

Keith

[iceblox]

Thanks for all your help guys!

 

model isn't an id Russell just a text string, i think this is where it was going wrong.

 

I tried your first example you gave me Keith and it worked a treat!

 

Just how i wanted it!

 

Thanks for you help guys it is much appreciated!

 

Thanks again,

Phil