SELECT ALL and flag [solved] 2006-06-01 - By Peter Lauri
Back
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=lists@(protected)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
