Please teach me about SQL.

Thank you for taking care of me.

I would like to get data with the same ID, date, and serial number, code 1234 and 1235.

tableA
ID Date Serial Number　　Code
120060223 1 1234
120060223 1235

 120060415 1111

1    20060415     3     1234
1    20060415     3     1235

1    20060415     3     1111



2    20060415     1     5555

2    20060415     1     1234
3    20060415     1     1235

4    20060415     2     1235
4    20060415     3     1235

4    20060417     1     1234
4    20060417     1     1235

6    20060415     1     1111

6    20060415     1     1234
6    20060415     1     1235

6    20060415     1     2345
6    20060415     1     5555

expected value

1    20060223     1     1234
1    20060223     1     1235

1    20060415     3     1234
1    20060415     3     1235

4    20060417     1     1234
4    20060417     1     1235

6    20060415     1     1234
6    20060415     1     1235

Environment
sql server 2008 R2
I would appreciate it if you could tell me how to obtain it on linq as well.
Thank you for your cooperation.

c# sql linq

2022-09-30 11:14

1 Answers

First of all, there is a condition that the ID, date, and serial number are the same, so you need to group them together.
To determine if each group meets the requirements, I think it would be better to limit the code with WHERE code IN('1234', '1235'), and then check the number of each group with COUNT(DISTINCT code) to obtain two types of code.
Therefore, the SQL to retrieve the group key is as follows:

SELECT ID, date, serial number
  FROM tableA
 WHERE CODE IN ('1234', '1235')
 GROUP BY ID, date, serial number
 HAVING COUNT (DISTINCT CODE) = 2

You can use this SELECT statement as a subquestion and combine it with JOIN, IN, and EXISTS to achieve the desired SQL.
For example, using INNER JOIN

SELECT*
  FROM tableAt
 INNER JOIN
(
    SELECT ID, Date, Serial Number
      FROM tableA
     WHERE CODE IN ('1234', '1235')
     GROUP BY ID, date, serial number
     HAVING COUNT (DISTINCT CODE) = 2
kON t.ID = k.ID AND t.Date = k.Date AND t.Continuous number = k.Continuous Number
 WHERE CODE IN ('1234', '1235')

It will look like this.
Implementation with LINQ is possible with the same policy.

2022-09-30 11:14

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656