How to not get duplicate records in cross join without using distinct?

Question

1.00/5 (1 vote)

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 don't want to use distinct but i don't know how to do it without getting duplicated records

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

Posted 20-Apr-20 7:57am

Member 14800672

Add a Solution

Comments

carloscs 20-Apr-20 15:01pm

With this schema you can't do it without using distinct or group by. [If you had a third PlaceLoc table with unique key columns (PlaceId, Loc) having all the place_locs you could do it].

However a slightly clear and more effective query is this one:

select p.PlaceId, p.Loc, d.DayId
from Place p, Day d
where not exists (select 1 from Place p2 where p2.PlaceId = p.PlaceId and p2.Loc = p.Loc and p2.DayId = d.DayId)
group by p.PlaceId, p.Loc, d.DayId

- it clearly tells what you wand to do - the other query is a bit obfuscated.
- the not exists correlated subquery should be a bit more optimized that the left join [allthough this can vary with differnte databases depending on the optimizer]

- note that the group by has the same effect as using distinct on the select part [I'm old school and prefer group by's :)]

Edit: query not tested, just a quick example

Member 14800672 20-Apr-20 16:26pm

I will try ur query now(what does select 1 mean, 1 refers to what?) I do have a table called PlaceLoc how can i use it in my query to get the correct result without distinct?

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)