How to create sql query for intersection between two datasets
I have these tables
create table Programs(
Id int identity(1,1) not null,
Days int not null,
Monday bit not null,
Tuesday bit not null,
Wednesday bit not null,
Thursday bit not null,
Friday bit not null,
Saturday bit not null,
Sunday bit not null)
create table ProgramItems(
Id int identity(1,1) not null,
ProgramId int not null,
ItemId int not null
IsActive bit not null)
create table Items(
Id int identity(1,1) not null,
Monday bit not null,
Tuesday bit not null,
Wednesday bit not null,
Thursday bit not null,
Friday bit not null,
Saturday bit not null,
Sunday bit not null)
create table CustomerProgram(
Id int identity(1,1) not null,
CustomerId int not null,
ProgramId int not null,
StartDate datetime not null)
when the user defines the program, he must do the following 1- defining the days of the program (Mon, Tue, Wed, Fri), for example 2- to select program items
I want to display all the items corresponding to those days in a grid like
Items available in Mon, Tue, Wed, Thu will be shown in a grid for selection, since the program time has a common with the days of days (Mon, Tue, Wed)
but if an item is available, like (Thu, Sat), it shouldn't appear in the grid.
my problem is how to continue this query in sql?
select *
from Items
where Id not in(select ItemId from ProgramItems
where ProgramId=1)
/* here i should pick only items that match with the program days*/
early
source to share
try this code
declare @program int
set @program=1
select *
from Items i, Programs p
where not exists(select 1 from ProgramItems where p.Id=ProgramId and i.Id=ItemId) and
((p.Monday=1 and i.Monday=p.Monday) or
(p.Tuesday=1 and i.Tuesday=p.Tuesday) or
(p.Wednesday=1 and i.Wednesday=p.Wednesday) or
(p.Thursday=1 and i.Thursday=p.Thursday) or
(p.Friday=1 and i.Friday=p.Friday) or
(p.Saturday=1 and i.Saturday=p.Saturday) or
(p.Sunday=1 and i.Sunday=p.Sunday))
where p.Program=@program
DEMO works here
source to share
The following return items have been installed at least one of Monday
, Tuesday
or Wednesday
:
select i.*
from Items i
where Id not in(select ItemId from ProgramItems where ProgramId = 1) and
(Monday = 1 or Tuesday = 1 or Wednesday = 1);
Below are the items where all three days are installed:
select i.*
from Items i
where Id not in(select ItemId from ProgramItems where ProgramId = 1) and
(Monday = 1 and Tuesday = 1 and Wednesday = 1);
EDIT:
If you have a specific program, you can do:
select i.*
from Items i join
Programs p
on p.id = @ProgramId and
(i.Monday >= p.Monday and i.Tuesday >= p.Tuesday and i.Wednesday >= p.Wednesday and
i.Thursday >= p.Thursday and i.Friday >= p.Friday and
i.Saturday >= p.Saturday and i.Sunday >= p.Sunday
)
where not exists (select 1
from ProgramItems pi
where pi.ProgramId = p.id and pi.ItemId = i.Id);
source to share
Surely this is just a matter of a simple connection?
Select I.* FROM Items AS I
INNER JOIN ProgramItems AS PI
ON PI.ItemId = I.Id
INNER JOIN Program AS P
ON P.Id = PI.ProgramID
WHERE (I.Monday = 1 OR I.TuesDat = 1 OR I.WednesDay =1) --you get the picture
AND P.Id = 1
But I have to question the duplication of Monday and Sunday battlefields in the element and program tables: why should it exist in both places? In fact, I would drop the where clause (I.day = 1, etc.) and populate the calendar based on whether the selected values are 1 or 0 in the code.
source to share