6

Let's say we have the table "letters" like so:

a | b  
-----  
0 | 0  
0 | 1  
1 | 0  
1 | 1

And the following select statement:

SELECT val1, val2, x.a. val3
  FROM table1, 
       table2, 
       (SELECT a 
          FROM letters 
         WHERE a=b 
           AND a=0) x, 
       table3
 WHERE table1.val1 = table2.val1
   AND table1.val2 = table3.val3

I need the embeded SELECT statement (x) to return:

0  
NULL  
NULL  
NULL

Instead of what is currently returning which is 

0

Then I want to be able to use that in the big SELECT statement. I hope this makes sense!

Improve this question
5
  • 5
    Your query makes no sense, with respect to your question. Apart from the poor formatting, it mentions three tables, table1, table2, and letters. Your question only mentions one table. Fix the question. It might also help if you explain what you are trying to do, because there are no doubt better queries. Commented Aug 7, 2015 at 13:55
  • All that matters is that I get the result for the embedded select statement. I put the rest of the query there just to show that I need the result for yet another select. SELECT val1, val2, x.a. val3, will return nothing if x.a doesn't exist. I could post my exact querry but it's pretty complicated. Commented Aug 7, 2015 at 14:03
  • It's a bit unclear to me what exactly you're after, but if you don't want non-matching results to show from your sub-select, why not move it to a JOIN? Commented Aug 7, 2015 at 14:06
  • Could you write an example of how that would work in the presented case? Commented Aug 7, 2015 at 14:14
  • Are you intentionally looking for a cross join between subquery X and the joins on tables 1, 2 and 3? If so subquery X should be defined as @dash inidicated by using the CASE statement instead of a where clause. You might consider reordering the from list such that subquery X is last (after table3 instead of before), and rewriting the query using ANSI Join syntax to make your intent more explicit: from table1 join table2 on table1.val1 = table2.val2 join table3 on table1.val2 = table3.val2 cross join (subquery) x. Commented Aug 7, 2015 at 16:04

5 Answers 5

Reset to default
5

There's a couple of ways you can go about this;

Trivial, using the CASE statement;

SELECT
    CASE 
        WHEN a = b AND a = 0 THEN a
        ELSE NULL
    END
FROM x

Returns:

0
NULL
NULL
NULL

Using a LEFT JOIN:

SELECT  X1.a
FROM    x 
LEFT JOIN   (SELECT a, b FROM x WHERE a = b AND a = 0) AS X1 ON x.a = X1.a AND x.b = X1.b

Returns:

0
NULL
NULL
NULL
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

The WHERE clause acts as a filter defining which rows are returned, but what you are asking is to have all rows returned, and apply a function on the returned rows to define the values returned for each row.

SELECT table1.val1
     , table1.val2
     , x.a
     , table3.val3
  FROM table1
  JOIN table2
    on table1.val1 = table2.val1
  JOIN table3
    on table1.val2 = table3.val3
 CROSS JOIN (SELECT CASE WHEN a = b AND a = 0 THEN a END AS a
               FROM letters) x
Improve this answer

Comments

0

going off of Dash's comment: 

SELECT
CASE 
    WHEN a = 1 OR b = 1 THEN Null
    ELSE 0
END
FROM x

as this would catch your second situation of b = 1, a = 0 and wanting a return of NULL.

Improve this answer

Comments

0

Remember, if you're using a GROUP BY clause and you want to conditionally replace values with NULL based on some condition, you might need to use a subquery in your FROM clause

Improve this answer

Comments

-3

If you want more than one column returned in the inner select, you'd need to specify those columns in the inner select as well...

(SELECT a, NULL 'colname1', NULL 'colname2'
          FROM letters 
         WHERE a=b 
           AND a=0) x,

You can access those columns using x.colname

If the columns could also have values besides NULL, you just need to add the proper joins and return the column values as a normal select statement would.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.