Gurhan:
<snip>
> I was wondering if you'd wanna use temporary tables to accomplish it..
Actually, another person on this list came up with the following:
SELECT s1.site_id
FROM site_category AS s1
JOIN site_category AS s2
WHERE s1.category_id=10
AND s2.category_id=12
AND s1.site_id=s2.site_id
It worked like a charm!
Thanks to all who replied!
Steve
> You may wanna do:
>
> CREATE TEMPORARY TABLE tmp1 SELECT * FROM site_category WHERE
> category_id=$category_id_1;
>
> ok this will give us the records that matches $category_id_1 in category_id
> field put them into a temporary table called tmp1.... Then do:
>
> CREATE TEMPORARY TABLE tmp2 SELECT * FROM site_category WHERE
> category_id=$category_id_2;
>
> and this will give us the records matching $category_id_2 in category_id
> field and put them into a temporary table called tmp2..
>
> Now you have 2 temporary tables, tmp1 and tmp2 as shown below:
>
> tmp1:
> +------+------+------+
> | sci | si | ci |
> +------+------+------+
> | 1 | 2 | 10 |
> | 4 | 4 | 10 |
> +------+------+------+
>
> tmp2:
> +------+------+------+
> | sci | si | ci |
> +------+------+------+
> | 3 | 4 | 12 |
> | 5 | 5 | 12 |
> +------+------+------+
>
> Now you can use use join syntax to find the si value thats common to the
> both tables...
>
> select * from tmp1, tmp2 where tmp1.si=tmp2.si;
>
> Does this work for you??
>
> Gurhan
>
> -----Original Message-----
> From: Summit [mailto:[EMAIL PROTECTED]]
> Sent: Monday, March 18, 2002 9:22 PM
> To: [EMAIL PROTECTED]
> Subject: [PHP-DB] SQL statement - PHP/mySQL - brain fart
>
> 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.]
>
> TIA,
>
> Summit
>
> ====================================================
> There is no such thing as a stupid person -
> there are only those who choose not to learn!
> Summit - [EMAIL PROTECTED]
> ====================================================
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
--
--------------------------------------------------
Peak to Peak Trail and Wilderness Links
Steve Fry - LinkKeeper - [EMAIL PROTECTED]
http://www.peaktopeak.net
--------------------------------------------------
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
