query sql between two tables

Question

i tried query but no have search answer, i have two tables

i want design a query in Linq that get all data that have register in table 2 with value in number field 1 AND value 3 AND value 4 , in this example i get data of the table 1

thanks for you collaboration

why votes negatives ? – Martin Chinome Sep 21 '17 at 15:14 — Martin Chinome, Sep 21 '17 at 15:14

score 1 · Accepted Answer · answered Sep 21 '17 at 18:15

Here's how I'd go about it - I wrote it in SQL because I'm WAY better at that than I am at LINQ, but converting from one to the other should be straightforward:

select 
    t1.id
    , t1.value
from table1 as t1
    inner join table2 as t21
        on t21.idTable1 = t1.id
        and t21.number = 1
    inner join table2 as t23
        on t23.idTable1 = t1.id
        and t23.number = 3
    inner join table2 as t24
        on t24.idTable1 = t1.id
        and t24.number = 4

This joins to a subset of Table 2 three times, once for each value that you want. Using "inner join" makes the logic equivalent to an AND.

score 0 · Answer 2 · answered Sep 21 '17 at 15:07

0

by using sub query

select * from table_1 where id in (select idtable1 from table_2 where number in (1,3,4))

by joints select * from table_1 t1 join table_2 t2 on t1.id=t2.idtable1 where t2.number in (1,3,4)

answered Sep 21 '17 at 15:07

Sagar T

89
1
1
11

score 0 · Answer 3 · answered Sep 21 '17 at 15:07

it would be

select id,idTable1,number 
       from 
       table2 
 where idTable1 = 1

if i'm understanding the question correctly. Your question is a bit hard to grasp. IF there should be a join in there its

   select b.id,a.Idtable1,a.number
     from table1 b ,table2 a 
     where b.id = a.Idtable1

score 0 · Answer 4 · answered Sep 21 '17 at 15:14

0

If I am not mistaken about what you want, this might work. You need to join the to tables and then check for the condition that you want using where.

SELECT a.id, a.value
FROM TABLE1 a
INNER JOIN TABLE2 b on a.id=b.idTable1
WHERE b.number in (1,3,4)
AND a.id = 1;

answered Sep 21 '17 at 15:14

Deja

346
2
13

thanks for you answer, but it is wrong because with sentence "in", i get data only to meets a conditions, i want get register that have number 1 and other register in number 3 and other register in value 4, i think that with sentence "union" could working – Martin Chinome Sep 21 '17 at 15:19

score 0 · Answer 5 · answered Sep 21 '17 at 17:13

This first query assumes that table2.number is unique for a given id. That might not be true. If it is:

select table1.id, table1.value
from table1 
join table2 
  on table1.id = table2.idTable1
group by table1.id, table1.value
having count(*) = 3
where table2.number in (1,3,4)

If 'number' could be a repeating value (is not unique for each idTable1), then we need to make sure we're joining on distinct values in table2:

select table1.id, table1.value
from table1 
join (select distinct table2.idTable1, table2.number where table2.number in (1,3,4)) t2 
  on table1.id = t2.idTable1
group by table1.id, table1.value
having count(*) = 3

query sql between two tables

5 Answers5