Posted by Dave on 05/23/05 18:28

Hi,

I was wondering if anyone can help me with this query. I have two tables.
table_1 is a master table that contains all records. table_2 contains IDs of
some records from table_1 and flags from those records. I'd like to query
all records and set the flags for them with following test: if a record
exists in table_2, set its flag from FLAG col. else set flag to '0'. return
all flags as col MY_FLAG. Below is the structure and a query I have so far.

Many thanks for any help,

Dave

table_1
ID NAME
-------- -----------------
10001 10001 name
10002 10002 name
10003 10002 name

table_2
ID FLAG
-------- -------
10001 1
10002 0

desired result:
ID NAME MY_FLAG
10001 10001 name 1
10002 10002 name 0
10003 10002 name 0

The following would give me an ERROR 1109: Unknown table 'table_2' in field
list

SELECT table_1.ID, table_1.NAME if(table_1.ID = table_2.ID, table_2.FLAG, 0)
AS MY_FLAG
FROM table_1
WHERE table_1.ID LIKE '1000%' ORDER BY table_1.ID;

if I try the following query, it returns incorrect cols for rows:
SELECT table_1.ID, table_1.NAME if(table_1.ID = table_2.ID, table_2.FLAG, 0)
AS MY_FLAG
FROM table_1, table_2
WHERE table_1.ID LIKE '1000%' ORDER BY table_1.ID;

returns:
ID NAME MY_FLAG
10001 10001 name 1
10001 10001 name 0
10002 10002 name 0
10003 10002 name 0

[Back to original message]