List:General Discussion« Previous MessageNext Message »
From:Peter Lauri Date:June 1 2006 2:25pm
Subject:RE: SELECT ALL and flag [solved]
View as plain text  
Solved it by:

SELECT table_a.name , if( table_a.id = table_b.table_a_id, 1, 0 ) AS
theindicator
FROM table_a
LEFT OUTER JOIN table_b ON ( table_a.id = table_b.table_a_id ) 

/Peter


------------

Hi,

I have a table table_a and table_b:

table_a {
id
name
}

table_b {
table_a_id
b_value
}

Table A is a table with names, and table B is a table with values for a
specific name (optional, therefore a specific table).

I would like to select all records in A, done by:

SELECT name FROM table_a;

Returns:
Peter
Johan
Fredrik

But then I also would like to have a flag that flags if table_b, I want it
to return this if Peters id is the only one matching in table_b:

Peter		1
Johan		0
Fredrik	0

I tried:

SELECT name, if(table_a.id=table_b.table_a_id, 1, 0) AS indid FROM table_a
JOIN table_b;

But that generates multiple rows of the records in table_a. I tried GROUP BY
in combination with ORDER BY, but I did not manage to get it to work.

How can I do that if() without having to action do a join, I just want to
check if it exist, and then give value 1 or 0.

Anyone with ideas?

/Peter


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
RE: SELECT ALL and flag [solved]Peter Lauri1 Jun