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]

Re: MYSQL select question

Dave wrote:[color=blue]
> 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[/color]

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]

Re: MYSQL select question

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" <BeateUndGeoff@ t-online.de> wrote in message
news:d6stmv$qi3 $04$2@news.t-online.com...[color=blue]
> Dave wrote:[color=green]
> > 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,[/color][/color]
table_2.FLAG, 0)[color=blue][color=green]
> > 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[/color]
>
> SELECT table_1.ID, table_1.NAME if(table_1.ID = table_2.ID, table_2.FLAG,[/color]
0)[color=blue]
> 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[/color]

[Geoff May]

Re: MYSQL select question

Dave wrote:[color=blue]
> 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.[/color]

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]

Re: MYSQL select question

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" <BeateUndGeoff@ t-online.de> wrote in message
news:d6suvd$q7s $01$1@news.t-online.com...[color=blue]
> Dave wrote:[color=green]
> > Thanks Geoff,
> >
> > Did you mean:
> > SELECT table_1.ID, table_1.NAME if(table_1.ID = table_2.ID,[/color][/color]
table_2.FLAG, 0)[color=blue][color=green]
> > 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.[/color]
>
> 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[/color]