Look at 2 tables and pull value from only one other tables

[johnisotank]

Hi all, could someone explain how I get this please..

TableA has fields 'Code', 'Value'
TableB has fieds 'Code', 'Value'

I want to query both tables and say this...

If value from TableB = 9999.99 then give me the value from TableA.

So with the following data..

TableA
Code, Value
1A, 50.00
1B, 75.00

TableB
Code, Value
1A, 9999.99
1B, 100.00

Result set would be

TableC
Code, Value
1A, 50.00
1B, 100.00

Many thanks
John

 

[r937]

SELECT a.code
     , COALESCE(NULLIF(b.value,9999.99),a.value) AS value
  FROM TableA AS a
LERFT OUTER
  JOIN TableB AS b
    ON b.code = a.code

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[johnisotank]

Hi, thanks very much for that - I am sure that will work and I appreciate the quick response but I have just been informed of something that may make things simpler (or not!)

There is in fact only ONE TABLE.

It has 3 columns (Code, Value, Group)

There are 2 possible Group value (1 or 2).

Each Code appears twice (One for each group value) and so the query should say

'Show all values for each code where the group = 2 but if the value returned is 9999.99 then show the value from the group 1.

So if the table look like this..

TableA
Code, Value, Group
1A, 100.00, 1
1A, 500.00, 2
1B, 750.00, 1
1B, 9999.00, 2

the output would be

Code, Value
1A, 500.00
1B, 750.00

Hope that makes sense and apologies for the incorrect information to start with. I am going to try and use your code as a base for doing it with the new information but if it's something you could easily explain I would really appreciate it.

Thanks
John

 

[simian336]

Having problems posting code.... Here is the first part.

select * from code
where [group] = 2 and value < 9999.00

Simi

 

[simian336]

here is the second...

select * from code
where [group] =1 and code in (
select code from code
where [group] = 2 and value = 9999.00)

Simi

 

[simian336]

select * from code
where [group] = 2 and value < 9999.00

union_all

select * from code
where [group] =1 and code in (
select code from code
where [group] = 2 and value = 9999.00)

Simi

 

[simian336]

Sorry for the multiple posts, the site did not like...

Union_all

Just take out the underscore character.

Simi