How to group boolean expressions in a WHERE clause

[lyealain]

select * from cbomnew 
where PartNumber not in 
  (select PartNumber from cbom_filtered)
and comCode= 'PCEC' || comCode='LOX' || comCode='CCEC' 
 

i wan to select those partnumber which not in cbom_filtered.. but why the partnumber which exist in cbom_filtered still appear...?? my code correct???

[Atli]

Because you need to group your boolean expressions together, or else it will evaluate them seperateley and return any row that matches any of them.

This is how your query should look:

select * from cbomnew 
where PartNumber not in 
  (select PartNumber from cbom_filtered)
and  
(    comCode= 'PCEC'
  OR comCode='LOX'
  OR comCode='CCEC' 
)
 

[Atli]

I have edited the thread's title to better describe it's contents.
Please read the Posting Guidlines before posting

Moderator

[lyealain]

Pls advice me...

i have PartNumber and SupplierName as my primary key..

[PHP]"select * from cbomnew where PartNumber not in (select PartNumber from cbom_filtered) and (comCode= 'PCEC'or comCode='LOX'or comCode='CCEC' )"[/PHP]

how do i put like

select PartNumber and SupplierName not in( select PartNumber from cbom_filtered) ?????

i wan to compare 2 data which are partnumber and suppliername.. how do i do that...

[pbmods]

Heya, lyealain.

select PartNumber and SupplierName not in( select PartNumber from cbom_filtered) ?????

You'll want to use WHERE NOT EXISTS

[lyealain]

[PHP] SELECT * FROM cbomnew WHERE NOT EXISTS (
SELECT * FROM cbom_filtered
WHERE cbomnew.partnumber = cbom_filtered.partnumber
AND[/b] cbomnew.suppliername = cbom_filtered.suppliername)
where (comCode= 'PCEC' or comCode='LOX' or comCode='CCEC' );[/PHP]

the above code give me error!!!!

i wan to select * where (comCode= 'PCEC' or comCode='LOX' or comCode='CCEC' ) and with these 2 constraints

[PHP]WHERE cbomnew.partnumber = cbom_filtered.partnumber
AND cbomnew.suppliername = cbom_filtered.suppliername)[/PHP]

[pbmods]

Heya, lyealain.

You have an errant '[/b]' in your SQL query. Is this intentional?

[lyealain]

ohh.. sorry.. ignore that [/b]..

[pbmods]

Heya, lyealain.

What error does MySQL give you when you run that query?

[lyealain]

hi.. very sorry for late reply...

i have updated my code to be like this...
[PHP]
dim qryFilter, rsReport
qryFilter= "select * from cbomnew where PartNumber + SupplierName not in (select PartNumber + SupplierName from cbom_filtered)and (RFQ='"&rfq&"')and (DateUploaded='"&lastup&"') and comCode IN (Select Distinct ComCode From comcode_table) "
[/PHP]
my idea is if the above not eof... then insert into cbom_filtered

but the above sentence keep inserting the same data into my cbom_filtered..

i have select something where not in the other table.. i wan to compare both PartNumber + SupplierName with cbom_filtered

cbomnew table: contains
partnumber: 123 SupplierName = angel
partnumber: 456 SupplierName = holy

in cbom_filtered table: contains
partnumber: 123 SupplierName = devil
partnumber: 456 SupplierName = ghost

if in cbom_filtered table: contains
partnumber: 123 SupplierName = angel
then don insert..
else .. insert..

[lyealain]

help help... experts pls help...thanks thanks thanks..hahahhaha

[pbmods]

Try this:

SELECT * FROM `cbomnew` WHERE NOT EXISTS(SELECT * FROM `cbom_filtered ` WHERE (`cbom_filtered`. `PartNumber ` = `cbomnew `.`PartNumber`) OR (`cbom_filtered`. `SupplierName ` = `cbomnew `.`SupplierName`)) AND (`RFQ` = '" & rfq & "') AND (`DateUploaded` = '" & lastup & "') AND EXISTS(SELECT * FROM `comcode_table` WHERE `comcode_table`.`ComCode` = `cbomnew`.`ComCode`)

Make sure your `cbomnew`, `cbom_filtered` and `comcode_table` tables are indexed on `PartNumber`, `SupplierName`, `RFQ`, `DateUploaded` and `ComCode`.

http://dev.mysql.com/doc/refman/5.0/...ubqueries.html

[lyealain]

[PHP]WHERE (`cbom_filtered`. `PartNumber ` = `cbomnew `.`PartNumber`) OR (`cbom_filtered`. `SupplierName ` = `cbomnew `.`SupplierName`)
[/PHP]
thanks.. should i put 'or' / 'and' for the above statement that u specified..

i think is AND??? because i wan to include both partnumber and suppliername to compare with other table at the same time

[pbmods]

Heya, lyealain.

i think is AND??? because i wan to include both partnumber and suppliername to compare with other table at the same time

The query I posted will return a result if either one exists. If you only want to return results that have both values, then you are correct; you'll want to change that to AND.