How to get not existing records

Question

0.00/5 (No votes)

See more:

I hay this table Day

DayId   Name
---   ----
1      sun
2      mon
3      tue
4      wed

And this table Place

PlaceId  Loc  DayId
1      1     1
1      1     2
1      2     1
2      1     3
3      1     2
3      1     4

i want to to return the days that are not used for each (PlaceId and LocId) so the result should be as follows

PlaceId  Loc  DayId
1      1     3
1      1     4
1      2     2
1      2     3
1      2     4
2      1     1
2      1     2
2      1     4
3      1     1
3      1     3

What I have tried:

I tried this but it's not working

select distinct Place.PlaceId,Place.Loc, Place.DayId from place
left join (
select DayId from Day where DayId not in(
select distinct dayId from Place) 
) q on q.DayId<>Place.DayId

Posted 19-Apr-20 22:17pm

Member 14800672

Updated 19-Apr-20 23:13pm

v5

Add a Solution

Comments

Daniele Rota Nodari 20-Apr-20 4:51am

The result (third table) is NOT the representation of what you ask.
All the days from the first table are used in the second table (all DayId values are present).
Maybe you should clarift what do you mean for "used" or "not used".
Does "used" mean that a combination of PlaceId+Loc has that specific DayId?

Member 14800672 20-Apr-20 4:56am

yes i mean what is not used for PlaceId and Loc Combined. (i updated the question). Can you please help?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Daniele Rota Nodari · Accepted Answer · 2020-04-19T23:14:00

Solution 1

SQL

select distinct Place.PlaceId, Place.Loc, Day.DayId
from Place
cross join Day
left join Place AS X
on Place.PlaceId = X.PlaceId
and Place.Loc = X.Loc
and Day.DayId = X.DayId
where x.DayId is null

This query firstly produces the cross join between Place and Day (from Place cross join Day), in order to generate all possible combinations.
Then try to join each (produced) record with the matching one within Place (left join Place).
Then excludes all record where a match is not found (where x.DayId is null).

If you like, you can wrap the cross join into a subquery in order to separate its logic from the left join

SQL

select _All.* from
(
select distinct Place.PlaceId, Place.Loc, Day.DayId
from Place, Day
) as _All
left join Place AS X
on _All.PlaceId = X.PlaceId
and _All.Loc = X.Loc
and _All.DayId = X.DayId
where x.DayId is null

Posted 19-Apr-20 23:14pm

Daniele Rota Nodari

Comments

Member 14800672 20-Apr-20 6:23am

Perfect.. Thank you :D

Member 14800672 20-Apr-20 13:39pm

Is there a way to do it without distinct and still not getting duplicated records?

Daniele Rota Nodari 20-Apr-20 14:39pm

The records are duplicated, then you need to instruct the query processor to avoid repetitions.
An alternative to DISTINCT is GROUP BY, but id would not add value to the query; it would render it longer and less readbale.
The only way to avoid duplication is to prevent it by having a table containing only the key combination records; that is, a table with only PlaceId and Loc fields, populated without repetitions; that would become a master/parent table.

Member 14800672 20-Apr-20 14:59pm

I do have a table that has both placeId and loc called placeLoc where exactly should i add this in our code?

Daniele Rota Nodari 21-Apr-20 6:01am

My previous reply was lost! :(
PlaceLoc substitutes Place everywhere you are using it to fetch the unique combinations.
That means inside the CROSS JOIN (and inside _All). After substitution, the DISTINCT keyword will no longer make difference and you can remove it.
You are using "select DISTINCT PLACE......" to gather exactly what is already inside PlaceLoc.