CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
W de wee
W de wee

Reputation: 13

SQL query adding rows on result - sql server 2012

In my Order table I have for each Order three tasks (id_task). If an id_address matches id_address 14 then I want to show all the tasks of that specific id_order.

id_order | id_task | id_address
 1       |   556   | 12
 1       |   557   | 14 
 1       |   558   | 11
 2       |   559   | 56
 2       |   560   | 88 
 2       |   561   | 77

results when I match id_address = 14

id_order | id_task | id_address
 1       |   556   | 12
 1       |   557   | 14 
 1       |   558   | 11

How do I do this?

I do now:

select id_order, id_task, id_address
from order
where id_address = 14

Of course this doesn't work. I will only get task 557. But I also want to have the other results from id_order. But how?

Upvotes: 0

Views: 48

Answers (3)

neer
neer

Reputation: 4082

You can as the below:

SELECT 
    A.* 
FROM 
    Order A INNER JOIN 
    Order B ON A.id_order = B.id_order 
WHERE
    B.id_address = 14

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use EXISTS:

select id_order, id_task, id_address
from order t1
where exists (select 1
              from order t2
              where t1.id_order = t2.id_order and t2.id_address = 14)

Upvotes: 2

Tedo G.
Tedo G.

Reputation: 1565

Here is the simple solution:

select 
      id_order
    , id_task
    , id_address 
from 
    [order] 
where 
    id_order IN (
            select 
                id_order 
            from 
                [order] 
            where   
                id_address = 14)

Upvotes: 3

Related Questions