CODEWITHSUNDEEP
sql
lapots
lapots

Reputation: 13395

return pair of values instead of one value from two queries

I've got two queries that return single result. They look something like this

// query 1
SELECT A.id FROM tableA A
    INNER JOIN tableB B
        ON B.id = A.id
WHERE b.status = 'ACTIVE'   

// query 2
SELECT C.id FROM tableC C
WHERE c.status = 'ACTIVE'

How to combine them and make return the pair of values instead of one value from different queries? I mean to get something like [A.id, C.id]

Currently I have to use two queries in the applications and I want to combine them into one.

Upvotes: 0

Views: 93

Answers (4)

praveen kumar
praveen kumar

Reputation: 13

You can join your second query with your first query as follows, so that you will get two (A.id, C.id) values in one query...

SELECT A.ID,C.ID FROM (SELECT A.ID FROM table_A A INNER JOIN table_B B ON A.ID=B.ID WHERE B.STATUS='A')A INNER JOIN table_c C ON C.ID=A.ID WHERE C.STATUS='A';

Upvotes: 0

payal takawale
payal takawale

Reputation: 1

You can run following query which work fine for me:

select t1.id as aid ,t2.id as cid
from (
       SELECT A.id 
       FROM tableA A
       INNER JOIN tableB B ON B.id = A.id
       WHERE b.status = 'ACTIVE'
     ) t1 
full outer join (
      SELECT C.id 
      FROM tableC C
      WHERE c.status = 'ACTIVE'
) t2  on t1.id=t2.id

Upvotes: 0

zarruq
zarruq

Reputation: 2465

As your question is not clear, so i assume that you either needids from mentioned queries in one row or in different rows, you can use union all/union (provided that datatypes are compatible or implicitly convertible and duplicates or allowed or not) as below.

Combining Result in different rows.

SELECT A.id 
FROM tableA A
INNER JOIN tableB B
ON B.id = A.id
WHERE b.status = 'ACTIVE'   

union all

SELECT C.id 
FROM tableC C
WHERE c.status = 'ACTIVE'

Combining Result in Single Row.

select max(id1), max(id2)
from(
    SELECT A.id as id1, NULL as id2
    FROM tableA A
    INNER JOIN tableB B
    ON B.id = A.id
    WHERE b.status = 'ACTIVE'   

    union all

    SELECT NULL, C.id 
    FROM tableC C
    WHERE c.status = 'ACTIVE' 
    ) t;

SAMPLE DEMO

Upvotes: 1

Lee
Lee

Reputation: 848

I think like this will do

SELECT (SELECT A.id FROM tableA A
         INNER JOIN tableB B
         ON B.id = A.id
         WHERE b.status = 'ACTIVE'   
       ) as 'query1',
       (
         SELECT C.id FROM tableC C
         WHERE c.status = 'ACTIVE'
       ) as 'query2'

Upvotes: 2

Related Questions