Old database with Related data, problem with my mysql query

Question

i have a litte problem with my sql-query:

I have a old database (more than 30 Years old) with a table (test):

TABLE: *test*
ID   SNR    DATE
---------------------------
1     1     1911-11-11
2     1     1911-11-11
3     1     2019-04-17

4     2     1911-11-11
5     2     1911-11-11
6     2     1911-11-11

7     3     1911-11-11
8     3     1911-11-11
9     3     2017-01-20

10    4     1911-11-11
11    4     1911-11-11
12    4     1911-11-11

et cetera

Each record with the same SNR are related

for example:

ID 1,2,3 are Related AND ID 4,5,6 are Related

I need the records where the Date is only 1911-11-11

for example:

SNR: 2 AND 4

my query:

SELECT * FROM test GROUP BY SNR AND DATE != 1911-11-11 ORDER BY ID

Is it possible to do this with a mysql query?

I hope you can help me

best regards

Is it possible to do this with a mysql query?... Have you tried your query to see what you get? - change it to SELECT * FROM test WHERE 'DATE' != "1911-11-11" GROUP BY SNR ORDER BY ID - here DATE is quoted because it is a reserved word — B001ᛦ
– B001ᛦ, Commented Apr 17, 2019 at 12:50
@B001ᛦ to quote DATE you should use backticks not single quotes... — Nick
– Nick, Commented Apr 17, 2019 at 12:54
Yes but backticks were used for the query comment. Thanks for mentioning @Nick — B001ᛦ
– B001ᛦ, Commented Apr 17, 2019 at 12:55
@B001ᛦ I don't think you got what I meant. Your query should have been SELECT * FROM test WHERE `DATE` != "1911-11-11" GROUP BY SNR ORDER BY ID — Nick
– Nick, Commented Apr 17, 2019 at 12:57

forpas · Accepted Answer · 2019-04-17 12:54:59Z

2

You get the snrs that you want with this query:

select snr from test
group by snr
having sum(date = '1911-11-11') = count(*)

so use it as a subquery for your table:

select * from test
where snr in (
  select snr from test
  group by snr
  having sum(date = '1911-11-11') = count(*)
)
order by id

answered Apr 17, 2019 at 12:54

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nick Over a year ago

I was in the process of making a demo for an answer when you beat me to it, so here it is anyway db-fiddle.com/f/oeWgXY2fQi92Mr4L9SS7Et/0

Collectives™ on Stack Overflow

Old database with Related data, problem with my mysql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related