By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,405 Members | 1,579 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,405 IT Pros & Developers. It's quick & easy.

MYSQL select question

P: n/a
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

Jul 17 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Jul 17 '05 #2

P: n/a
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

Jul 17 '05 #3

P: n/a
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
Jul 17 '05 #4

P: n/a
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

Jul 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.