looking for the AND version of IN

[.josh]

okay this is my current query:

select providers.provider_name, services.service_name from providers
inner join maintable on maintable.provider_id = providers.provider_id
inner join services on services.service_id = maintable.service_id
where services.service_id in('x','y','z')
order by providers.provider_name, services.service_name

it generates data whenever services.service_id = x,y OR z

this is fine for one type of search that i need. but for another search option, i need to get it to only generate the info if it equals x,y AND z

anybody know how? is it simple like, ONLY IN ('x','y','z') (doesn't work, i tried that :) )

or do i have to do it individually like

where services.service_id = 'x' and services.service_id = 'y' and services.service_id = 'z' ?

[shoz]

[quote]
or do i have to do it individually like

where services.service_id = 'x' and services.service_id = 'y' and services.service_id = 'z' ?
[/quote]
One record's "services_id" cannot be x,y and z all at the same time. Perhaps you want something else?

[.josh]

oops. yeh that's not what i meant, lol.

okay, i'm going to explain my form and code but i swear it's a sql question :)

i have a form and it shows a list of services that providers offer. each service has its own checkbox. the user can select one or more checkboxes to find providers based off their selection(s).

currently i have it setup to where if for example the user checkmarks 3 of the checkboxes, the query will return every provider that has at least one of those three services that the user checkmarked.

here is the query string for that:

[!--coloro:blue--][span style=\"color:blue\"][!--/coloro--]
select providers.provider_name, services.service_name from providers
inner join maintable on maintable.provider_id = providers.provider_id
inner join services on services.service_id = maintable.service_id
where services.service_id in ('1','2','3')
order by providers.provider_name, services.service_name
[!--colorc--][/span][!--/colorc--]

now I want to make a second search filter for the user. I want them to be able to click on those same 3 checkboxes and return only providers that provide all 3 of those services.

now, i've been piddling around with the above code all morning, and so far i've come up with this query string:

[!--coloro:blue--][span style=\"color:blue\"][!--/coloro--]
select providers.provider_name from providers inner join maintable on maintable.provider_id = providers.provider_id where maintable.service_id in ('13','15','17') GROUP BY providers.provider_name having count( * ) = 3 order by providers.provider_name
[!--colorc--][/span][!--/colorc--]

this is an example string, based on the amount of checkboxes and which checkboxes the user checks. now this query actually works properly in that it returns the correct provider name(s).

for instance, if
[!--coloro:green--][span style=\"color:green\"][!--/coloro--]
provider1 offers service1, service2, service3
provider2 offers service2, service3, service4
provider3 offers service1, service3, service4
[!--colorc--][/span][!--/colorc--]
and i want to find [b]any[/b] provider that offers service1 [b]or[/b] service4, the first query string will return
[!--coloro:green--][span style=\"color:green\"][!--/coloro--]
provider_name | service_name
provider1 | service1
provider3 | service1
provider3 | service4
[!--colorc--][/span][!--/colorc--]

now let's say that i want to return [b]only[/b] the providers that offer service1 [b]and[/b] service4. the 2nd query works just fine. it will return

provider3

now since i already have the service info in a variable, i know that provider3 at least offers service1 and service4. but i want to be able to display all of the services offered, with the ones from the query highlighted or something.

so what this means is i need the query to somehow return service3. well, i think it would be easier to return all services associated with the provider than tto do some kind of select * not like selected checkbox services or something.

here is what the page looks like so far in action (it's not pretty i'm trying to make it work first)

[a href=\"http://www.chroniclesofwar.com/providersearch.php\" target=\"_blank\"]provider search[/a]

for example, if you click the 2nd radio button "by only" and then check the 1,3 and 5 boxes, you should see the query string echoed and under that, "provider3" provider3 is the only provider that offers service1,2 [b]AND[/b] 3 but i don't want it to just display the provider name i want it to also query to select all of the services associated with it

[shoz]

If you want the list of services_id associated with the provider you can use [a href=\"http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html\" target=\"_blank\"]GROUP_CONCAT[/a](requires MYSQL >= 4.1).
[code]
SELECT
providers.provider_name, GROUP_CONCAT(maintable.service_id) AS s_id_list
FROM
providers
INNER JOIN
maintable
ON
maintable.provider_id = providers.provider_id
WHERE
maintable.service_id IN ('13','15','17')
GROUP BY
providers.provider_name
HAVING
count( * ) = 3
ORDER BY
providers.provider_name
[/code]
GROUP_CONCAT by default will give a comma delimited list of the values. In PHP you'd be able to seperate them with explode
[code]
$id_array = explode(',', $row['s_id_list']);
[/code]
If you you're using a version of MYSQL below 4.1 you should issue another query using the provider_id(s) you just retrieved
[code]
SELECT
p.provider_id, m.service_id
FROM
providers AS p
INNER JOIN
maintable AS m
ON
m.provider_id = p.provider_id
WHERE
p.provider_id IN ($list_of_provider_ids_retrieved)
ORDER BY
p.provider_id
[/code]
You can then use PHP to create an array of the service_id's for each provider
[code]
$prov_services = array();
while ($row = mysql_fetch_assoc($result))
{
    $prov_services[($row['provider_id'])][] = $row['service_id'];
}
[/code]
Above the provider_id is being used as the key. You can use [a href=\"http://www.php.net/foreach\" target=\"_blank\"]foreach[/a] to go through the provider_id's and their services.
[code]
foreach ($prov_services as $prov_id=>$serv_array)
{
    print '|'.$prov_id.':'
    foreach ($serv_array as $s_id)
    {
         print $s_id.',';
    }
}
[/code]

[fenway]

You don't specifically need to use the GROUP_CONCAT() function; you can always retrieve just the name/UID of the provider. You passed in the UID list, so there's no need to make the DB give them back to you.

[code]SELECT p.provider_name, COUNT(*) AS cnt
FROM providers AS p
INNER JOIN maintable AS m ON ( m.provider_id = p.provider_id )
WHERE m.service_id IN ('13','15','17')
GROUP BY p.provider_name
HAVING cnt = 3
ORDER BY p.provider_name[/code]

[shoz]

I see your point. Even after reading your reply it took me a while to see what you were saying. My mind went from seeing what the group by did to what the where clause did, but I didn't look at them together. Although I think I've understood the question while you've understood the query.

The question was (I think), how to retrieve the service_ids of the services each provider was associated with on top of the ones in the list.
[code]
SELECT
p.provider_name, m.service_id
FROM
providers  AS p
INNER JOIN
maintable AS m
ON p.provider_id = m.provider_id
WHERE
p.provider_id
IN
    (
    SELECT p2.provider_id
    FROM providers AS p2
    INNER JOIN maintable AS m2 ON ( m2.provider_id = p2.provider_id )
    WHERE m2.service_id IN ('13','15','17')
    GROUP BY p2.provider_name
    HAVING COUNT(*) = 3
    )
ORDER BY p.provider_name
[/code]
Crayon, note that the 4.0 solution can still be used.

[fenway]

Well, now I've gone back and re-read the initial post, and perhaps "HAVING cnt >= 3" is appropriate as well. Even so, a second query back to the DB to find all matching service_ids would still work.

[.josh]

[!--quoteo(post=372081:date=May 7 2006, 01:16 PM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ May 7 2006, 01:16 PM) [snapback]372081[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I see your point. Even after reading your reply it took me a while to see what you were saying. My mind went from seeing what the group by did to what the where clause did, but I didn't look at them together. Although I think I've understood the question while you've understood the query.

The question was (I think), how to retrieve the service_ids of the services each provider was associated with on top of the ones in the list.
[code]
SELECT
p.provider_name, m.service_id
FROM
providers  AS p
INNER JOIN
maintable AS m
ON p.provider_id = m.provider_id
WHERE
p.provider_id
IN
    (
    SELECT p2.provider_id
    FROM providers AS p2
    INNER JOIN maintable AS m2 ON ( m2.provider_id = p2.provider_id )
    WHERE m2.service_id IN ('13','15','17')
    GROUP BY p2.provider_name
    HAVING COUNT(*) = 3
    )
ORDER BY p.provider_name
[/code]
Crayon, note that the 4.0 solution can still be used.
[/quote]

this does exactly what i need it to do! thanks shoz, fenway! btw it seems to return the same results whether i do COUNT(*) = 3 or COUNT(*) >= 3 i'm not exactly sure why that is, so i flipped a coin and went with >=

if it's not too much trouble, can you explain to me what's going on in this query string? I test it and it gives me what i need and all, but I have no idea how it does what it does and i'd like to understand it.

for instance,

SELECT p.provider_name, m.service_id FROM providers AS p

i have no idea why you did 'p.' and 'm.' as i have no tables/columns called p or m. i thought maybe it was some kind of sql shorthand but when i put in providers.provider_name i get an error. so what does the p.blah m.blah mean/point to/whatever?

also i'm still not sure how joins work. i mean, i know it's needed for linking the tables together with the _id's but i just don't fully understand how that works. is the where the p.blah and m.blah come in?

[shoz]

[quote]
Well, now I've gone back and re-read the initial post, and perhaps "HAVING cnt >= 3" is appropriate as well.
[/quote]
The WHERE clause limits the result to only those in the list so the result won't have the other service ids associated with the provider_id and the count can never be greater than 3. That's similar to what I meant by me seeing the result of the "GROUP BY" and the "WHERE" seperately, but not together. Although eliminating the id list from the WHERE and using GROUP_CONCAT with an order by and using SUBSTRING in the "HAVING" to test the list could work. Using an "IF()" with the id list in the WHERE to create a specific count may also work.
[quote]
it seems to return the same results whether i do COUNT(*) = 3 or COUNT(*) >= 3 i'm not exactly sure why that is, so i flipped a coin and went with >=
[/quote]
Both will work, but "=" makes your intention clear. ">=" works because since the count won't be > 3(Unless your tables are structured differently. ie allowing a provider_id to have duplicated service_id references. Use a Unique index to avoid that.), The ">=" will only be true on the "=" condition, making it act as it would if it was just an "=" alone.
[quote]
SELECT p.provider_name, m.service_id FROM providers AS p

i have no idea why you did 'p.' and 'm.' as i have no tables/columns called p or m. i thought maybe it was some kind of sql shorthand but when i put in providers.provider_name i get an error. so what does the p.blah m.blah mean/point to/whatever?
[/quote]
It is shorthand. The "AS" creates an alias for the tables, and you use that alias instead of the table name whenever the table name is needed.

[a href=\"http://dev.mysql.com/doc/refman/4.1/en/select.html\" target=\"_blank\"]SELECT SYNTAX[/a] (Do a search for "alias")
[quote]
if it's not too much trouble, can you explain to me what's going on in this query string? I test it and it gives me what i need and all, but I have no idea how it does what it does and i'd like to understand it.
[/quote]
The only part of the query I think you may have trouble with is the subquery. The first part of the query with the JOINs uses the syntax I think you're used to (except for the aliases).
[code]
SELECT
p.provider_name, m.service_id
FROM
providers  AS p
INNER JOIN
maintable AS m
ON p.provider_id = m.provider_id
[/code]
The subquery uses the query you created, to find the providers that are associated with all the service_ids in the list. We test the providers against this list in the WHERE clause using "IN". [a href=\"http://dev.mysql.com/doc/refman/4.1/en/any-in-some-subqueries.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/any...subqueries.html[/a]
[code]
WHERE
p.provider_id
IN
    (
    SELECT p2.provider_id
    FROM providers AS p2
    INNER JOIN maintable AS m2 ON ( m2.provider_id = p2.provider_id )
    WHERE m2.service_id IN ('13','15','17')
    GROUP BY p2.provider_name
    HAVING COUNT(*) = 3
    )
ORDER BY p.provider_name
[/code]