MYSQL select question

[Dave]

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

[Geoff May]

Dave wrote:

Hi,

[snipped]

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

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
and table_1.ID = table_2.ID;

MfG

Geoff.

--
Unofficial F1 Database: http://glibs.ssmmdd.co.uk/
Update: 22nd May, 2005
USENET Email address is a spam trap, send Emails to address in the DB

[Dave]

Thanks Geoff,

Did you mean:
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%' and table_1.ID = table_2.ID
ORDER BY table_1.ID;

If so this works but won't return the row:
10003 10002 name 0

As the query is restricted by matching ID cols in both tables.

Thanks,

Dave

"Geoff May" <Be***********@t-online.de> wrote in message
news:d6*************@news.t-online.com...

Dave wrote:

Hi,

[snipped]

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
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
and table_1.ID = table_2.ID;

MfG

Geoff.

--
Unofficial F1 Database: http://glibs.ssmmdd.co.uk/
Update: 22nd May, 2005
USENET Email address is a spam trap, send Emails to address in the DB

[Geoff May]

Dave wrote:

Thanks Geoff,

Did you mean:
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%' and table_1.ID = table_2.ID
ORDER BY table_1.ID;

If so this works but won't return the row:
10003 10002 name 0

As the query is restricted by matching ID cols in both tables.

Then you need to use the JOIN, something like this:

SELECT table_1.ID, table_1.NAME, if(table_1.ID = table_2.ID,
table_2.FLAG, 0)
FROM table_1
left left join table_2 on (table_1.ID = table_2.ID)
where table_1.ID LIKE '1000%'
ORDER BY table_1.ID;

MfG

Geoff.

--
Unofficial F1 Database: http://glibs.ssmmdd.co.uk/
Update: 22nd May, 2005
USENET Email address is a spam trap, send Emails to address in the DB

[Dave]

The query below worked as expected. Geoff, thanks a lot!

SELECT
table_1.ID,
table_1.NAME,
IF(table_1.ID = table_2.ID, table_2.FLAG, 0)
FROM table_1
LEFT JOIN table_2 on (table_1.ID = table_2.ID)
WHERE table_1.ID LIKE '1000%'
ORDER BY table_1.ID;
"Geoff May" <Be***********@t-online.de> wrote in message
news:d6*************@news.t-online.com...

Dave wrote:

Thanks Geoff,

Did you mean:
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%' and table_1.ID = table_2.ID
ORDER BY table_1.ID;

If so this works but won't return the row:
10003 10002 name 0

As the query is restricted by matching ID cols in both tables.

Then you need to use the JOIN, something like this:

SELECT table_1.ID, table_1.NAME, if(table_1.ID = table_2.ID,
table_2.FLAG, 0)
FROM table_1
left left join table_2 on (table_1.ID = table_2.ID)
where table_1.ID LIKE '1000%'
ORDER BY table_1.ID;

MfG

Geoff.

--
Unofficial F1 Database: http://glibs.ssmmdd.co.uk/
Update: 22nd May, 2005
USENET Email address is a spam trap, send Emails to address in the DB