Now I understand what you want. (For people who try to unsub me from this list: Sorry I only try to help, if you are not intressted in it pleasee dont read)
If you start using MySQL 4.1 (Only ALPHA) then you can use subqueries like this:
select *
from table1
where id not in
( select refid from table2
where thirdvalue = 'de')
problem solved.
But if you use an older version the only solution is to use a temporary table or make it in code.
First you select al the values from table2 with thirdvalue 'de', walk trough this dataset while looking in table1 if theres a corresponding value.
I think there are no other options. I have tried to rewrite the subquery to a left join, but i think it isn't possible, because he thirdvalue = 'de' makes a problem. It is no problem to get all rows from table1 who have no corresponding record in table2 with a left join:
"select table1.id
from table1
left join table2 on table1.id = table2.refid
where table2.refid IS NULL "
But you have the problem that there is a corresponding record in table2. I''ve tried to solve this with a union who returns a NULL value . But I can't get the right results.
regards, Hans
At 16:13 15-10-03 +0200, you wrote:
2003-10-15 kl. 15.57 skrev Hans van Dalen:
If I'm right you want to do a left join. For example:
Table1: Id value 1 100 2 200 3 300
Table2: Id RefId OtherValue 1 1 9000 2 1 10000 3 2 8000
If you do this: select Table1.Value, Table2.OtherValue from Table1 left join Table2 on Table2.RefId = Table1.Id
then the result is: Value OtherValue 100 9000 100 10000 200 8000 300 NULL
Almost, but I would like to do it from a subselect of Table2 that also has the column thirdvalue:
Table2: Id RefId OtherValue ThirdValue 1 1 9000 dk 2 1 10000 de 3 2 8000 dk
This table is unfinished as is in my application.
What I'ld like to do is to find out that there is no OtherValue in table2 for Table1.id = 2 where table2.Thirdvalue _SHOULD_ be 'de'.
That is, for each id in Table1, _force_ join on table2 where ThirdValue = 'de' but since I actually need the id's in Table1 for which there is no corresponding Thirdvalue = 'de' I get a big problem…
Sincerely
Victor
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
