On Tuesday 19 March 2002 10:22, Summit wrote:
> For some reason I keep thinking that this should be simple - but I just
> can't seem to figure it out. Help??? Please??? [I've been working on
> it for two days now.]
>
> Overview: What I'm trying to do is query one table by passing it two
> different variables - and return only the results that are COMMON to
> both variables. [PHP 4.1.2/mySQL 3.23.44/FreeBSD]
>
> Assume I have a site_category table:
>
> -------------------
> site_category
> -------------------
> site_category_id
> site_id
> category_id
> -------------------
>
> Perhaps a dump of this looks something like this:
>
> ---------------- ------- -----------
> site_category_id site_id category_id
> ---------------- ------- -----------
> 1 2 10
> 2 3 11
> 3 4 12
> 4 4 10
> 5 5 12
> 6 5 14
> ---------------- ------- -----------
>
> Using values for the varibles I'm passing to the query (see below) of
> ...
>
> $category_id_1 = 10
> $category_id_2 = 12
>
> ... the result I'm looking for is:
>
> site_id = 4
>
> ... as this is the only site_id which is common to both ...
>
> category_id = 10
> category_id = 12
>
> I've tried a bazillion variations on the following query:
>
> SELECT sc.*
> FROM site_category sc
> WHERE (sc.category_id = $category_id_1 OR sc.category_id =
> $category_id_2)
>
> Breaking out the parts ...
>
> So, if category_id_1 = 10, I'm returned:
>
> site_id = 2
> site_id = 4
>
> So, if category_id_2 = 12, I'm returned:
>
> site_id = 4
> site_id = 5
>
> How can I get that "4" which you can clearly see is common to both of
> the parts above?
>
> But just about no matter how I write my queries, I keep getting:
>
> site_id = 2
> site_id = 4
> site_id = 4
> site_id = 5
>
> Or if use SELECT DISTINCT we get:
>
> site_id = 2
> site_id = 4
> site_id = 5
>
> [I want that extra 4 that the "DISTINCT" threw out!!!]
>
> I keep thinking that I can do this in a single query - but I don't know
> for sure. I've tried sub-selects with no luck [E.g. IN()]. Do I need
> to do something with arrays and array_intersect? [I've even tried
> messing with the PHP3 hacks for array_unique - trying to reverse them 'n
> stuff - but still no luck.]
>
> Does anyone have a simple solution? [I'll even take a hard solution -
> but I keep thinking that I'm just looking at the the wrong way.]
SELECT sc.*
FROM site_category sc
WHERE sc.category_id = $category_id_1
AND sc.category_id = $category_id_2
Or am I missing something?
--
Jason Wong -> Gremlins Associates -> www.gremlins.com.hk
/*
Let's just be friends and make no special effort to ever see each other again.
*/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
