How to know which lines are not in certain groups?

Asked

Viewed 38 times

-1

I would like to know which ids are not present in certain groups for a certain period of time. For example:

SELECT id from table1

Results in the following exit:

id 
1
2
3
4
5

And the consultation :

select Id, DateStart,DateFinish from table2 
group by DateStart,DateFinish, Id
order by Id, DateStart

Results in :

Id DateStart DateFinish
1   2017        2019
2   2017        2019
3   2017        2019 
2   2014        2016
4   2014        2016

The output I want to know is which ids of Tabela1 are not in the respective groupings of table2?

The desired exit would be something like:

id DateStart DateFinish
4   2017        2019
5   2017        2019 
1   2014        2016
3   2014        2016
5   2014        2016
sql

  • Friend, your example got a little confused, I answered based on what I could interpret, in case you missed something please let me know in the comments so that I can adjust my answer.

1 answer

2

To solve this problem, we can use the EXISTS clause, which according to the documentation:

Specifies a sub-sum to be tested for the existence of lines.

So, for the example you gave, the query would look like this:

SELECT * FROM table1 t1
WHERE NOT EXISTS (
select * from table2 t2
WHERE t2.id=t1.id AND t2.DateStart=t1.DateStart AND t2.DateFinish=t1.DateFinish
);

This way, it will select the values of table 1 that are not present in table 2. You can see working and test here in SQL Fiddle. I recommend reading this article from iMasters: Understanding the Exists and Not Exists clauses.

  • Unfortunately my problem is how it is in the example. In table 1 I only have the ids and in table 2 I have the ids and dates. I can’t compare the dates to know if you have the ID there or not. I have to somehow group by dates and check for each group if the id of table 1 does not exist in each group of table 2.

  • But the ID in Table 2 is the ID of the records in Table 1?(Foreign key)

  • Yes, the ids of the second table are foreign key from the first table.

  • Remove the check of the dates within select should then resolve, from what I understood.

  • But it will give me which ids of table 1 do not exist in table 2. But I want to know which ids of table 1 do not exist in each group of table 2. It is as if table 1 were product ids and table 2 were products sold for years, then in that case I would be wondering which products ceased to be sold each year and which products were never sold.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.