SQL Query Help

[allenvpn]

Hello all,

I have a query which simply won't yield the result I want. I'm currently running on RedHat Linux 7.2 and MySQL version 3.23.45.

I have two tables:

attributekey
ID int(2)
NAME varchar(30)

productattribute
ID char(30),
ATTRIBUTEID int(2)
ATTRIBUTEVALUE varchar(30)

attribute key has 2 records:
1, Color
2, Width
3, Height

productattribute has 1 record:
A123, 1, RED

I want to create a query which will compare the productattribute.ATTRIBUTEID to attributekey.ID and list out attributekey.ID and attributekey.NAME which does not match.

If I execute this query:
"select attributekey.ID, attributekey.NAME from attributekey, productattribute where productattribute.ATTRIBUTEID <> attributekey.ID && productattribute.ID = 'A123'";

This will list out all 3 rows from attributekey when it should only list 2. I hope this is understandable :-).

TIA,
Allen.

[ultraslacker]

This is explained in the mysql manual.

5.4.1 Sub-selects

The following will not yet work in MySQL:

SELECT FROM table1 WHERE id IN (SELECT id FROM table2);
SELECT FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2 where table1.id=table2.id);

However, in many cases you can rewrite the query without a sub-select:

SELECT table1. FROM table1,table2 WHERE table1.id=table2.id;
SELECT table1. FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL

[allenvpn]

Wow, this is a tricky one. I do wish MySQL would support sub-select, but until then I'll have to figure something out. I'll just have to revise a new process rather than the current one I'm using.

Thank you for the input.