July 3, 2017 at 10:04 am
I know it has probably been asked and answered but i am not sure how to search for it.
Currently using a dynamic string for a query to retrieve rows that match any of the items
'Select * from customers where apps in ('01', '02', '03',....)'
i am letting my users select any number of apps and include them in the query but now i am needing to select the customers that have multiple apps.
like 01 and 12 and 27
the apps will be dynamically created and can currently be values of '01' thru '56' and that can change in the future.
July 3, 2017 at 10:06 am
roy.tollison - Monday, July 3, 2017 10:04 AMI know it has probably been asked and answered but i am not sure how to search for it.
Currently using a dynamic string for a query to retrieve rows that match any of the items
'Select * from customers where apps in ('01', '02', '03',....)'
i am letting my users select any number of apps and include them in the query but now i am needing to select the customers that have multiple apps.
like 01 and 12 and 27
the apps will be dynamically created and can currently be values of '01' thru '56' and that can change in the future.
OK, but what is your question?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 3, 2017 at 10:11 am
The solution depends heavily on exactly how the data is stored. If your customers table has one row for each app it's associated with, that's one solution, whereas if your customers table has just one row with multiple app values in one or more fields, that's a rather different solution. Please provide table CREATE TABLE statements and INSERT statements with sample data, along with the expected output from the sample data, and someone will surely be able to help.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 3, 2017 at 10:12 am
Phil Parkin - Monday, July 3, 2017 10:06 AMroy.tollison - Monday, July 3, 2017 10:04 AMI know it has probably been asked and answered but i am not sure how to search for it.
Currently using a dynamic string for a query to retrieve rows that match any of the items
'Select * from customers where apps in ('01', '02', '03',....)'
i am letting my users select any number of apps and include them in the query but now i am needing to select the customers that have multiple apps.
like 01 and 12 and 27
the apps will be dynamically created and can currently be values of '01' thru '56' and that can change in the future.OK, but what is your question?
how to query dynamic values to include only row that match ALL items.
'Select * from customers where apps in ('01', '02', '03',....)' will return all customers with any of the listed apps.
i am needing only customers with ALL apps queried.
Select * from customers where apps in ('01', '22', '51') i dont want all the customers with app 01 or 22 or 51 i want all customers WITH apps 01, 22, 51
July 3, 2017 at 10:21 am
sgmunson - Monday, July 3, 2017 10:11 AMThe solution depends heavily on exactly how the data is stored. If your customers table has one row for each app it's associated with, that's one solution, whereas if your customers table has just one row with multiple app values in one or more fields, that's a rather different solution. Please provide table CREATE TABLE statements and INSERT statements with sample data, along with the expected output from the sample data, and someone will surely be able to help.
The customer table has a row for each app.
customer_01 , 01
customer_01 , 21
customer_01 , 51
Customer_02, 12
customer_03, 01
customer_03 , 20
July 3, 2017 at 10:22 am
roy.tollison - Monday, July 3, 2017 10:12 AMhow to query dynamic values to include only row that match ALL items.
'Select * from customers where apps in ('01', '02', '03',....)' will return all customers with any of the listed apps.
i am needing only customers with ALL apps queried.
Select * from customers where apps in ('01', '22', '51') i dont want all the customers with app 01 or 22 or 51 i want all customers WITH apps 01, 22, 51
Not sure exactly how the 'dynamic' requirement affects things here, but does this form work?SELECT *
FROM customers c where c.CustomerId IN (
SELECT c2.CustomerId
FROM customers c2
WHERE c2.apps IN (
'01'
,'22'
,'51'
)
GROUP BY c2.CustomerId
HAVING COUNT(c2.CustomerId) = 3
)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 3, 2017 at 10:30 am
that will work like i want.
thank you so very much .
July 3, 2017 at 10:33 am
roy.tollison - Monday, July 3, 2017 10:12 AMPhil Parkin - Monday, July 3, 2017 10:06 AMroy.tollison - Monday, July 3, 2017 10:04 AMI know it has probably been asked and answered but i am not sure how to search for it.
Currently using a dynamic string for a query to retrieve rows that match any of the items
'Select * from customers where apps in ('01', '02', '03',....)'
i am letting my users select any number of apps and include them in the query but now i am needing to select the customers that have multiple apps.
like 01 and 12 and 27
the apps will be dynamically created and can currently be values of '01' thru '56' and that can change in the future.OK, but what is your question?
how to query dynamic values to include only row that match ALL items.
'Select * from customers where apps in ('01', '02', '03',....)' will return all customers with any of the listed apps.
i am needing only customers with ALL apps queried.
Select * from customers where apps in ('01', '22', '51') i dont want all the customers with app 01 or 22 or 51 i want all customers WITH apps 01, 22, 51
Possibly something like this:SELECT C.*
FROM CUSTOMERS AS C
CROSS APPLY (
SELECT COUNT(*) AS app_count
FROM CUSTOMERS AS C2
WHERE C2.CustomerID = C.CustomerID
AND C2.apps IN ('01','22','51')
) AS CT
WHERE CT.app_count = 3
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply