• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

SQL to select certain fields then set a column 'price' depending is there is a match, or a difference 'price' if null


I need to be able to build a list of address references (UPRN) and cross match with another table looking up the same UPRN .

If there IS a match on the 2nd table then I need to set a price column to be 10.00 otherwise if there is no match (null) I want to set a price of 20.00

This code lists all the UPRN data from t_blpu and then if there is a UPRN match in t_refuse_garden_2014 also lists that in the column next to it.

Really all I want is every c_uprn from t_blpu then a set of prices next to each one depending if there is a match or not.

For info the c_uprn in t_blpu is prefixed '0000' hence the addition.

SELECT     t_blpu.c_uprn, t_refuse_garden_2014.green_uprn
FROM         t_blpu LEFT OUTER JOIN
                      t_refuse_garden_2014 ON t_blpu.c_uprn = '0000' + t_refuse_garden_2014.green_uprn
ORDER BY t_refuse_garden_2014.green_uprn DESC 

Open in new window

Neil Thompson
Neil Thompson
  • 2
1 Solution
Anthony PerkinsCommented:
Something like this perhaps:
SELECT  b.c_uprn,
		WHEN g.green_uprn IS NOT NULL THEN 10.00
		ELSE 20.00
	END Price
FROM    t_blpu b
        LEFT OUTER JOIN t_refuse_garden_2014 g ON b.c_uprn = '0000' + g.green_uprn
	g.green_uprn DESC 

Open in new window

Neil ThompsonSenior Systems DeveloperAuthor Commented:
Thanks Anthony, its giving me this error when I paste it and try to run it. Apologies my SQL is ok at getting things with a select and join but nothing else :(

Error in SELECT clause: expression near 'CASE'.
Unable to parse query text.
Neil ThompsonSenior Systems DeveloperAuthor Commented:
Found it, just needed a , before case, thank you so much, saved me days of searching trying to do this :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now