Re: get rows not present using join _AND_ where clause
Okay, 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 1 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 1 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 9000dk 2 1 1 de 3 2 8000dk 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]
get rows not present using join _AND_ where clause
I need to find out, using a join, which corresponding rows in a union that is missing. But it is among a certain subselect I want to do this, that is, among the rows where the column lang = 'uk' for example. The two tables looks like following: +---++---+ | id | betegnelse | enhed | +---++---+ | 00046 | 838718001064311911 | 5 | | 00120 | 641725001064311948 | 5 | | 01310 | 532898001064317190 | 5 | | 01320 | 535436001064317190 | 5 | | 01330 | 537895001064317190 | 5 | +---++---+ den andra ser ut som +--++--+--+ | id | relid | lang | text | +--++--+--+ | 5513 | 838718001064311911 | dk | m | | 5514 | 838718001064311911 | de | m | | 5515 | 838718001064311911 | uk | m | | 5517 | 641725001064311948 | dk | Stk. | | 5518 | 641725001064311948 | de | Stk. | +--++--+--+ As you can see, the second one holds the language-strings for the first table. But as you also can see I'm missing the last row in table two which should read: +--++--+--+ | id | relid | lang | text | +--++--+--+ | 5519 | 641725001064311948 | uk | Stk. | +--++--+--+ What I would like to do is to list all rows, with null values, where lang != 'dk' or 'de', for example, or rather where lang = 'uk' to find out which that are missing. (What I'm gonna do then is to list the missing texts for translation under a translation page in the admin-area. But thats at a later point - now I just have to select them) Best regards and lots of thanks in advance! Sincerely Victor // Malmoe and Copenhagen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: get rows not present using join _AND_ where clause
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 1 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 1 200 8000 300 NULL Kind regards Hans van Dalen At 15:37 15-10-03 +0200, you wrote: I need to find out, using a join, which corresponding rows in a union that is missing. But it is among a certain subselect I want to do this, that is, among the rows where the column lang = 'uk' for example. The two tables looks like following: +---++---+ | id | betegnelse | enhed | +---++---+ | 00046 | 838718001064311911 | 5 | | 00120 | 641725001064311948 | 5 | | 01310 | 532898001064317190 | 5 | | 01320 | 535436001064317190 | 5 | | 01330 | 537895001064317190 | 5 | +---++---+ den andra ser ut som +--++--+--+ | id | relid | lang | text | +--++--+--+ | 5513 | 838718001064311911 | dk | m | | 5514 | 838718001064311911 | de | m | | 5515 | 838718001064311911 | uk | m | | 5517 | 641725001064311948 | dk | Stk. | | 5518 | 641725001064311948 | de | Stk. | +--++--+--+ As you can see, the second one holds the language-strings for the first table. But as you also can see I'm missing the last row in table two which should read: +--++--+--+ | id | relid | lang | text | +--++--+--+ | 5519 | 641725001064311948 | uk | Stk. | +--++--+--+ What I would like to do is to list all rows, with null values, where lang != 'dk' or 'de', for example, or rather where lang = 'uk' to find out which that are missing. (What I'm gonna do then is to list the missing texts for translation under a translation page in the admin-area. But thats at a later point - now I just have to select them) Best regards and lots of thanks in advance! Sincerely Victor // Malmoe and Copenhagen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
