MySQL / SQL geeks out there!

[beanbag]

So it goes like this......

I've got two tables, "data" and "systems" with two sets of data however both tables have the columns, "node" and "domain" so I use these as a combined primary key.

The table "systems" gets updated all the time so I need to cross check the table "data" against it.

To do this I used the query:

SQL to choose matched records said:

select data.node, systems.priority, systems.revision from data, systems where systems.node = data.node AND systems.domain = data.domain AND systems.vendor = 'ms'

This works great! You all still with me!?

Now I want to select all the systems that don't match up so I used the following query:

SQL to select records that don't match said:

select data.node, systems.priority, systems.revision from data, systems where systems.node NOT LIKE data.node AND systems.domain NOT LIKE data.domain AND systems.vendor = 'ms'

How this doesn't work and I get about 700,000 odd results! So how do I make this work properly?

Thanks all!

>> Edited by beanbag on Friday 13th May 10:54

[jimbro1000]

try this:

select [fields]
from
tablea join tableb
on clausea and clauseb
where key not in
(
select key from
tablea join tableb
on clausea and clauseb
where constraint
)

[BliarOut]

What's your version of MySQL? Can you use a subquery which is basically the subset of what's left from the initial query?

Something like (And I'm guessing here!)
select data.node, systems.priority, systems.revision
from data, systems
where NOT (systems.node = data.node AND systems.domain = data.domain
AND systems.vendor = 'ms')

Or should that be !(etc)



I use forums.mysql.com when I get stuck. HTH.

[beanbag]

I'll give that a go! It's v4.1.10 btw.....

[beanbag]

jimbro1000 said:

try this: select [fields] from tablea join tableb on clausea and clauseb where key not in ( select key from tablea join tableb on clausea and clauseb where constraint )

Stupid question here but is "key" a variable and do I replace it with something?

[ATG]

Might need to be a little careful you're not producing cartesian products in your sub queries ... i.e. asking every possible pairing of a rows btwn the two tables.

I'd use an outer join and a test for Null be returned from one table.

Table X has columns a,b,d and table Y has columns a,b,e.

Matching rows are:

SELECT X.d, Y.e
FROM X JOIN Y ON (X.a = Y.a AND X.b = Y.b)

Rows in X that don't match in Y are:
SELECT X.d
FROM X OUTER LEFT JOIN Y ON (X.a = Y.a AND X.b = Y.b)
WHERE Y.e IS NULL

Rows in Y that don't match in X are:
SELECT Y.e
FROM X OUTER LEFT JOIN Y ON (X.a = Y.a AND X.b = Y.b)
WHERE X.d IS NULL