Merge two sql queries with if condition

Question

I want to merge two queries.

I have Query1 returning some Ids.

Then I have Query2 which firstly check if some Ids are present in table. If no then the result would be Ids returned by Query1.

If yes then I want common Ids which are returned by Query1 and Query2 like intersect of Ids returned by Query1 and Query2.

So how can I do this in a single query.

you might want to see this : http://stackoverflow.com/questions/38549/sql-difference-between-inner-and-outer-join See what is basically is called LEFT OUTER JOIN and also INNER JOIN for your requirements respectively — nawfal, Apr 09 '12 at 06:11
currently I am getting result of Query1 in a datatable and Query2 in other datatable. Then looping through these datatables getting common Ids and inserting it in a third datatables. I wanna to minimise this. — user1049021, Apr 09 '12 at 06:12
Yes instead you can go for one query which is pretty much the standard too.. Use JOINs.. You not thorough with that? — nawfal, Apr 09 '12 at 06:14
@nawfal - m clear with joins concept but thats not useful in this case. — user1049021, Apr 09 '12 at 06:14
you're right, i missed the point. which db are u using? this needs some kinda if else logic in the query (as far as i know). this can be done easily in sql server.. good question btw! — nawfal, Apr 09 '12 at 08:30
yes it can be done in sql.. i did it.. but m using both sql server and oracle.. In sql u can write if( select count(col) from table) >0 but in oracle u need to declare a variable to store the count and then the if condition.. This doesnot look good for inline query.. N i dnt wanna add sproc or function in db for such simple task as its not going to be reused. — user1049021, Apr 10 '12 at 05:04
Exactly, kinda hassle to do in mysql in which i tried - you require storeproc. TSQL looks cool here. Could you post the answer here? — nawfal, Apr 10 '12 at 07:19
you should definitely consider adding what database you use in these questions, else its meaningless. — nawfal, Apr 10 '12 at 07:20
@user1049021 You are a member for over a year now, have asked 10 questions and accepted NOT A SINGLE ONE. I cannot believe that all of them are not worthy and accepted hook. (If you don't know what I mean, see the FAQ.) — Angelo Fuchs, Apr 10 '12 at 21:05

score 1 · Answer 1 · answered Apr 11 '12 at 05:32

This can be achieved using the combination of DECODE and CASE statement


SELECT DISTINCT BT1.ID
  FROM BUFFER_TABLE_1 BT1, BUFFER_TABLE_2 BT2
 WHERE DECODE((SELECT COUNT(BT1.ID)
                FROM BUFFER_TABLE_1 BT1, BUFFER_TABLE_2 BT2
               WHERE BT1.ID = BT2.ID),
              0,
              'TRUE',
              (SELECT CASE
                        WHEN BT1.ID = BT2.ID THEN
                         'TRUE'
                        ELSE
                         'FALSE'
                      END
                 FROM DUAL)) = 'TRUE'
 ORDER BY BT1.ID

The main part is in decode used in where In case the count of matching IDs from both the tables is ZERO, then all IDs are returned from the 1st table(BUFFER_TABLE_1). However, in case, this count is greater than 0, then the IDs in both the table are matched and only common IDs are returned

Hope it helps

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

(For people who have already read my answer: It has been hugely re-edited because of the comments of @nawfal that I want to preserve)

First I want to clarify that I understood your problem correct.

You have the Parts:

select id from table1 That produces Result1
select id from table2 where <some condition> That produces Result2
select count(id) > 0 from table2 where <some condition> That decides what result to use

If 3. returns more than 1 then you want the rows that are returned by 1. AND 2. (but not the ones that are only returned by 1. OR only by 2.)

If 3. returns 0 then you want the results of 1.

-> The solution is to have 3. in a view and select it in a where clause of a union statement TWICE.

Like this:

SELECT t1.id FROM table1 t1 WHERE 
       (SELECT COUNT(t2.id) from table2 t2 where <some condition>) = 0
UNION
select t2.id from table2 t2 WHERE <some condition> 
       AND t2.id IN (SELECT t1.id FROM table1 t1)
       AND (SELECT COUNT(t2.id) from table2 t2 where <some condition>) > 0

one of the two parts will always be empty (because query 3. can't be = 0 and > 0 at the same time)

edited May 23 '17 at 12:22

Community

1
1

answered Apr 09 '12 at 06:19

Angelo Fuchs

9,825
1
35
72

my queries are: select * from table1 select * from table2 from table2 where what i want is: if( select count(*) from table2 from table2 where >0) then //intersect Ids from query1 and query2 else //Ids from query1 Hope now i made my question more clear – user1049021 Apr 09 '12 at 06:39
@user1049021 please post your queries in an edit to your question. – Angelo Fuchs Apr 09 '12 at 06:40
No Union all will give duplicate entries – user1049021 Apr 09 '12 at 08:13
@AngeloNeuschitzer this is exactly what came top off my head but it doesn't give him the desired result. When there is an entry in table2, the first query returns 0 records and union doesnt work anymore. He needs a kind of intersect in that scenario.. Few corrections: make union all to union, change `==` to `=`. what he needs is some kind of control flow logic – nawfal Apr 09 '12 at 08:29
@nawfal No, Union all will concat all selects within. If you ensure that the first query will only produce results if the second wont than you will always have either the first or the second but never both. – Angelo Fuchs Apr 09 '12 at 10:17
I understand what you mean, but thats not the OP's requirement. He wants an intersection of values from table1 and table2 if table2 happens to have some values in it, which your query doesnt give. Your query works when there is no id in table2. In which case union works.. – nawfal Apr 09 '12 at 11:22
Ok just tell me two things: one, what difference in your code makes if union all is made union? two, if `(select count(*) from table2 from table2 where ) == 0` is not true (which means there are some id values in table2), then how's your query going to meet OP's needs? I myself failed miserably though. I can think of anything which doesnt break the query into two separate ones.. – nawfal Apr 09 '12 at 11:24
first: union vs. union all: it seems like there is none here. I've confused it somehow... *scratches his head* sorry, I'll fix it. The other one: I see what you mean and why my solution doesn't fit (yet). I'll expand it. – Angelo Fuchs Apr 10 '12 at 20:45
@nawfal So, I've rewritten my answer, hopefully its more clear now. – Angelo Fuchs Apr 10 '12 at 21:02

Merge two sql queries with if condition

2 Answers2