Can I do this in mysql?

weird query question

[rlkanter]

I have:

Table a:
id

Table b:
contains1
contains2
contains3

Table c:
contains1
contains2
contains3

Is there a way to do a query that lists all a.id's that aren't in the contains in either table b or table c?

[hakre]

shure you can. you can left join 2 tables [dev.mysql.com] and the only return the rows where null values of the second table exist for the id.

in your case then only the problem is left that you don't have 2 table but 3 instead. so you need to union [dev.mysql.com] the 2 last tables first and use this as "one table" as as so called subquery [dev.mysql.com].

i know that this is not simple sql and you may need to dig around a bit to find the practical solution for your problem but i'm shure that you can do this with mysql. and: it's important for you to know what kind of mysql version you have.

[coopster]

I looked at this earlier and was thinking along the same lines, hakre, but how would you join the tables? There seems to be no "id" column in the secondary tables.

[mcibor]

WOuldn't it be better to work with NOT LIKE word? I tried to do this code, but resigned after 6th error.

I tried sth like:

SELECT id FROM a WHERE id NOT LIKE ((SELECT * FROM b) UNION (SELECT * FROM ));

But this supposedly doesn't work.
Michal Cibor

[hakre]

NOT LIKE(subquery)

won't help here, because you can not feed the compare part with a subquery i strongly assume (not checked right now 100% in the docs, but this seems very strange to me), the other part looks quite good. maybe this is in that direction:

SELECT id FROM a LEFT JOIN ((SELECT id FROM b) UNION (SELECT id FROM c)) AS d ON a.id = d.id WHERE d.id IS NULL;

[coopster]

That won't work though hakre, at least it shouldn't, according to the table column layout given -- there is no "id" column in the secondary files (see my earlier post).

[mcibor]

Would this do the trick?

SELECT id FROM a LEFT JOIN ((SELECT * FROM b) UNION (SELECT * FROM c)) AS d ON a.id = d WHERE d IS NULL;

on the other thought no. What about a temporary table made of columns of b and c? And then checking against it?

Michal

[hakre]

That won't work though hakre, at least it shouldn't, according to the table column layout given -- there is no "id" column in the secondary files (see my earlier post).

if there is no id or other field to compare with table 1 id, then you can never tell anything because there is no relation. i thought contains1 etc. means just, in row id there is 1. that's all. i can see no table layout then at all. maybe rlkanter should clarify this first.

[coopster]

Exactly ;)

[mcibor]

I thought that contains1-3 in tables b and c are fields which can contain a number refering to id in table a. Is that true?

Michal Cibor

[rlkanter]

Sorry for the delay in replying. I tried to only include the information I thought that was needed and probably left out, or didn't explain enough.

Well to start out I'm running mysql version 4.1

Here is some example data:
Table a
Id - 1
Id - 2
Id - 3
...

Table b
contains1 2, contains2 3, contains3 5
contains1 5, contains2 7, contains3 9

Table c
contains1 4, contains2 5, contains3 6
contains1 10, contains2 9, contains3 5

I want to display all a.id's that aren't in the contain fields of both table b and c.

As far why table b, and table c are the way they are, that's a bit harder to explain :) The best way to describe them is as a recipe. It needs a certain amount of items from tabl a, and if all those items are combined together they give one of various results. I'm just trying to find if any item can not be created.

I've tried a few different things such as:

select a.id from a where a.id not in ( select distinct a.id from a_b )
select a.id from a where a.id not in ( select contains1, contains2, contain3 from b )

with no success.

Thanks for taking the time to look into this, it's been driving me nuts on and off for the past week :)

[mcibor]

In the mysql manual 5.0.3-alpha I found some query that could possibly solve your problems, however as I tried to execute it there was some error. Maybe you'll have more luck!

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

According to the manual these two queries are the same.
However on my computer they aren't working.

Have a try!

Or maybe someone seas a mistake?

I'd be grateful!
Best wishes
Michal Cibor

[hakre]

mysql manual 5.0.3-alpha

that manual is for version mysql 5.0.3-alpha! i doubt that you have installed that version of database server on any webserver right now.

looks like that they put subselects as function parameters, very cool and maybe something you need on your wired tables. but stop. if you have UNION available, we could create a better comparison table with a UNION first. but i miss a primary key in table 2 and 3 to really trick around. do these 2 tables have a primary key next to the contains1-3 fields?