Thread: SQL select where case statement!

Hi

I need to SQL select a number from a table when a different table holds a certain value

e.g.

select number1 from table (table1) when value1 in table2 = true
select number2 from table (table1) when value2 in table2 = true
select number3 from table (table1) when value3 in table2 = true

thanks

Is this even possible?


i know it is for the same table but is it possible for a case to be in a separate one?

well i have an access database that i connect to using

sNWind = _
      "M:\Television and Broadband\CHANNEL ECONOMICS\Database\Database Actual\ChannelEconomics_Q3_2010.accdb"
      
   conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
      sNWind & ";"


and the the sql statement would be something like

rst.Open "SELECT [VALUE A] from TABLE A when [VALUE B] from TABLE B = 'TRUE'

all i want is some sql that gets a certain value from a table when a value in a seperate table is true..

for example
when value A from Table A is true
get Value B from Table B


the tables are not physically connected!

no, i have a load of different information in table B, but for this instance i'm only interested in one value! I see why this is confusing now, i have missed out a bit of information

i should say something like
[code}
IF (FOR ID x in TABLE B), VALUE B = True, THEN GET VALUE 1 From TABLE A where ID = Y
IF (FOR ID x in TABLE B), VALUE C = True, THEN GET VALUE 2 From TABLE A where ID = Y
IF (FOR ID x in TABLE B), VALUE A = True, THEN GET VALUE 3 From TABLE A where ID = Y
[/code]

Hmm not quite, the ID'S are different, but defined buy a user input, the query is then run lots of times with one the id's varied to extract a bunch of data on it. Thanks for your help, i may be able to work the rest out

as far as i can tell this is the right code:

rst.Open "Select (Iif(3D from ChannelBrand = 'true') then ([3D Subscribers] from OperatorDetail FROM OperatorDetail WHERE [Operator Name] = 'WARNER' AND Country = '" & cntry & "'), (Iif(HD from ChannelBrand = 'true') then ([HD Subscribers] from OperatorDetail FROM OperatorDetail WHERE [Operator Name] = 'WARNER' AND Country = '" & cntry & "'),(Iif(3D from ChannelBrand = 'true') then ([3D Subscribers] from OperatorDetail FROM OperatorDetail WHERE [Operator Name] = 'WARNER' AND Country = '" & cntry & "'),(Iif([Digtal/Analogue] from package = 'digital') then ([Digital Subscribers] from OperatorDetail FROM OperatorDetail WHERE [Operator Name] = 'WARNER' AND Country = '" & cntry & "'),(Iif([Digtal/Analogue] from package = 'analogue') then ([Analogue Subscribers] from OperatorDetail FROM OperatorDetail WHERE [Operator Name] = 'WARNER' AND Country = '" & cntry & "')", conn

oh god

okeydokey.. could you point me towards the right code please