Counting couples

Transact-SQL
1

Hi everyone,

I'v got two relations

rel1: date, number
rel2: number1, number2

number, number1 and number2 are values of integer, rel1.date is really a date.

for each date there are 8 numbers. I would like to know on how many days rel1.number contains rel2.number1 AND rel2.number2 (in different rows).

Can anyone help me, please?

Thanks in advance

yours,

LXus

2

can you post some sample data and the expected result ?

3

rel 1

date;number
2014/10/11;1
2014/10/11;5
2014/10/11;4
2014/10/11;2
2014/10/11;3
2014/10/11;7
2014/10/11;8
2014/10/11;19
2014/10/12;12
2014/10/12;4
2014/10/12;2
2014/10/12;23
2014/10/12;7
2014/10/12;1
2014/10/12;19
2014/10/12;3

rel 2
number1;number2
1;2
2;23
4;8
7;3

expected value
number1;number2;count
1;2;2
2;23;1
4;8;1
7;3;2

4

Try this

select t2.number1,t2.number2,count(*) as count from rel1 as t1 
inner join rel2 as t2
on t1.number=t2.number2 group by t2.number1,t2.number2