I have a table bawe_services. i want to fetch all data that match with given keys
like i have fields
id | Service_id |bawe_id
1 2 2
2 3 3
3 2 3
if i pass service =2 i need all record of service_id=2 if i pass service=1,2,3 than i want 0 rows because 1 service is not given by any bawe so. i got 0 rows.
I use this query
select * from aspnet_bawe_services where ser_id in(1,2,3)
Thanx in advance
The count of the parameters in the "in" statement must match the having equal number.
select bawe_id from [dbo].[aspnet_bawe_services]
where Service_id in (2)
group by bawe_id
having count(Service_id)=1;
bawe_id
-----------
2
3
select bawe_id from [dbo].[aspnet_bawe_services]
where Service_id in (2,3)
group by bawe_id
having count(Service_id)=2;
bawe_id
-----------
3
select bawe_id from [dbo].[aspnet_bawe_services]
where Service_id in (1,2,3)
group by bawe_id
having count(Service_id)=3;
bawe_id
-----------
(0 row(s) affected)
TRY THIS: It's really tedious but unique requirement and I think to accomplish this, we have to use function
1-Function returns distinct count of service_id
2-Function to split comma separated value and return in table format
--Function returns distinct count of service_id
CREATE FUNCTION [dbo].[getCount](@service_id varchar(500))
RETURNS INT
AS
BEGIN
DECLARE @count int
SELECT @count = COUNT(DISTINCT(t.service_id))
FROM tmptos t
INNER JOIN [dbo].[SplitValue](@service_id, ',') tt on t.service_id = tt.items
RETURN @count
END;
--Function to split comma separated value and return in table format --Function copied from --separate comma separated values and store in table in sql server
CREATE FUNCTION [dbo].[SplitValue](@String varchar(MAX), @Delimiter char(1))
RETURNS @temptable TABLE (items VARCHAR(MAX))
AS
BEGIN
DECLARE @idx int
DECLARE @slice varchar(8000)
SELECT @idx = 1
if len(@String)<1 or @String is null return
WHILE @idx!= 0
BEGIN
set @idx = charindex(@Delimiter,@String)
IF @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
IF(LEN(@slice)>0)
INSERT INTO @temptable(Items) values(@slice)
SET @String = right(@String,len(@String) - @idx)
IF LEN(@String) = 0 break
END
RETURN
END;
--Table with Sample Data
create table tmptos(id int, Service_id int, bawe_id int)
insert into tmptos values
(1, 2, 2),
(2, 3, 3),
(3, 2, 3)
declare @service_id varchar(50) = '2,3'
select *
from tmptos t
inner join [dbo].[SplitValue](@service_id, ',') tt on t.Service_id = tt.items
where [dbo].[getCount](@service_id) = (select count(distinct(items)) from [dbo].[SplitValue](@service_id, ','))
OUTPUT:
id Service_id bawe_id items
1 2 2 2
2 3 3 3
3 2 3 2
It's bit lengthy but works perfectly.
select * from aspnet_bawe_services
where Service_id in (1,2,3)
and
( select count(distinct Service_id) from aspnet_bawe_services where Service_id in (1,2,3) ) = 3
last number in query (in this case "3") is elements count, which you have in IN list.
You can get the service ids that you want using group by and having:
select service_id
from t
where bawe_id in (1, 2, 3)
group by service_id
having count(distinct bawe_id) = 3;
The "= 3" is the number of ids in the IN list.
You can then use in or join or exists to get the full records:
select t.*
from t
where t.service_id in (select service_id
from t
where bawe_id in (1, 2, 3)
group by service_id
having count(distinct bawe_id) = 3
);
