How to make a select within a condition?

Asked

Viewed 1,003 times

5

How can I create a script in SQL where a SELECT, and as the result of a column of this first, performs a certain condition of another table?

example:

select * from tabela as t
SE t.saldo = 0 entao WHERE t2.outracondicao = 'condicao'
sql sql-server query
  • 3

    Possible duplicate of Put If to Where

  • 1

    Could you put the structure of the tables in your question? There are no similar questions to yours here in the OS?

3 answers

5


SELECT * 
FROM tabela AS t
WHERE (t.saldo = 0 
       AND EXISTS (SELECT 1 
                   FROM tabela2 AS t2 
                   WHERE t2.outracondicao = 'condicao'))
  -- OR t.saldo <> 0

When the balance is equal to zero, the search will only be executed if the condition of the second table is met (as you did not show the structure of the tables, I kept in the answer the "information" that are in the question).
The commented excerpt (--OR t.saldo <> 0) can be used if you need to. It will execute the query if the balance is non-zero.

Another alternative is that the validation is done before consultation. If the tabela2 regardless of tabela, can be done like this:

IF EXISTS (SELECT 1 FROM tabela2 AS t2 WHERE t2.outracondicao = 'condicao')
BEGIN
    SELECT * 
    FROM tabela AS t
    WHERE t.saldo = 0 
END

At the suggestion of @Rbz, I created a online example for validation.

  • But if select is not recursive, how will it be "reading" the balance value, and "redoing" the filter (Where)?

  • In the case, saldo is only validated if the condition he needs the tabela2 exist.. then bring all values with zero balance (if t2.outracondicao = 'condicao')

  • Yes, but the t.saldo is from the main table. Each tuple will do a WHERE? And yet here SELECT 1 got confused. Please do a Sqlfiddle that I’m with a knot in my head! ahahah

  • It was a good idea. fiddle created.. Amended response ;]

  • 1

    My dear, now only that I’ve seen t2.outracondicao. That’s why you’re doing JOIN... I even deleted my other comment on the question! hahaha thanks!

3

in this case, if you have two fields in common within the two tables, you can use the WHERE IN

Let’s say there is the ID field in table2 that is also present in Tabela1. You could do it as follows:

SELECT * FROM tabela2 WHERE id IN (SELECT id FROM tabela1 WHERE saldo='0')

This query will fetch all records in table2 that are also present in Tabela1, but only with 0 balance.

  • Lucas, I think a different point to your answer is that you return everything from tabela2, but in the question he asks select * from tabela (different tables). Moreover, if id for a code (manual/controlled filling), the comparison is correct, but if it is a accountant (identity), possibly will be different in the two tables (one will not match the other)

  • rLinhares, yes, it would be interesting to know the structure of the tables, how they connect (and if they really do) and what the data application is. Thank you for the remark.

2

This way can help you:

SELECT t.* FROM tabela t
JOIN tabela2 t2
    ON t2.id = t.id
WHERE 
    t2.outracondicao = (CASE
                        WHEN t.saldo = 0
                            THEN 'condicao'
                        END)

  • I don’t know if I understood the solution very well, I found Join a little confused. Anyway, if the balance is non-zero, he would try to compare the t2.outracondicao with NULL, nay?!

  • This, he would compare with NULL, to bring everything it is necessary to make a ELSE t2.other. WHERE t2.otherformance = (CASE WHEN t.balance = 0 THEN 'condition' ELSE t2.otherformance END)

Browser other questions tagged sql sql-server query

You are not signed in. Login or sign up in order to post.