List results by true or false

[bruceoz]

Hi folks I need some help!

I have two tables the first contains account information, and the second contains flags that records in the account table may have.

These are joined by the account?id. One account record may have several flags from the flag table.

ie the following select

select f.flags from flag_table f inner join account_table a on f.account_id = a.account_id where a.account_id = 4328

could result in:

flags
------------
EV3
NMI
FRH
BLO
------------

I need to extract data from the account table (adresses etc) and have a column for each of the flags in the flag table and list if that particular flag is present.

for example

Account_id, FirstName, LastName, EV1,NMI,FRH,BLO
1 Bruce Farr T F T F

(T and F could be 1 and 0)

I have been trying to get my head around this but to no avail.

Any help is greatly appreciated

[sergb]

You can run a whole query that produces a scalar result and have it be returned and one column in the result set.

select
   account_id,
   firstname,
   lastname,
   case when exists(select * from flag_table where account_id = account_table.account_id and flag = 'EV1')
      then 1 else 0 end EV1,
   case when exists(select * from flag_table where account_id = account_table.account_id and flag = 'NMI')
      then 1 else 0 end as NMI
   -- and so on for each flag you want
from account_table

[bruceoz]

Thanks Sergb

worked well
Sorry I didn't get round to replying earlier