[SOLVED] self join?

[mihail]

Hi guys,

 

I'm a beginner in php/mysql and I need some help. I have a problem with a query.

 

Example: I have some cities and in every city there could be some stores.

 

A table (a relational table):

 

City_id store_id

1              1

1              2

2              1

3              1

3              2

3              3

 

My input is two city_ids ($city_id1 and city_id2). I have to select all unique store_ids that are in both $city_id1 and city_id2. For example if $city_id1 = 1 and $city_id2 = 2 a result should be 1.

 

Another: if $city_id1 = 1 and $city_id2 = 3 a result should be 1 and 2.

 

Thanks for your help

[worldworld]

I think your query must be :

 

$sql = "select store_id from tbl where City_id=\"$cid\"";

$sql_rs = mysql_query($sql);

while($rw = mysql_fetch_array($sql_rs))

{

  echo $rw["store_id"];

}

 

Try it...

[mihail]

No, that is not correct, but thanks for your help anyway.

 

Maybe I was not clear enough:

 

I have to select store_ids where city_id = $city_id1 (that is group 1 of store_ids), then I have to select  store_ids where city_id = $city_id2 (that is group 2 of store_ids) and the store_ids I need as a result are those distinct store_ids that are in both grups.

 

For example:

Group 1 store_ids – 1, 2, 4, 5, 9, 10, 11

Group 2 store_ids –2, 3, 5, 7, 9

Result – 2, 5, 9

 

God, I will bite my hand off with this one. I know there is something I'm missing here.

[fenway]

Why not just issue two select queries, and union distinct them?

[mihail]

Hm, I don't want ALL distinct store_ids from both groups, I only want those distinct store_ids that are members of both grups.

[effigy]

Why doesn't this work?

 

(select store_id from table where city_id = 1) union (select store_id from table where city_id = 2);

[mihail]

Why doesn't this work?

 

(select store_id from table where city_id = 1) union (select store_id from table where city_id = 2);

 

A result of this query is 1 and 2 but a result i'm looking for should be just 1.

 

(select store_id from table where city_id = 1) gives 1 and 2

(select store_id from table where city_id = 2) gives 1

union distinct of these 2 queries gives 1 and 2

 

And again, I only want those distinct store_ids that are members of both grups, in that case it is only store_id 1.

 

 

[effigy]

I think this is what you want, but I'm wondering if there's a better way...

SELECT store_id FROM (SELECT store_id, GROUP_CONCAT(city_id SEPARATOR ',') AS city_ids

FROM table GROUP BY store_id) AS store_city_set

WHERE FIND_IN_SET(1, city_ids)

AND FIND_IN_SET(2, city_ids);

[shoz]

I think this is what you want, but I'm wondering if there's a better way...

This should be better assuming it gives the result the OP is looking for.

SELECT
store_id, COUNT(*) AS cnt
FROM
tablename
WHERE
city_id IN($city_id1, $city_id2)
GROUP BY
store_id
HAVING
cnt = 2

 

A UNIQUE multi-column index should logically be on (cityid, storeid) and is required to ensure the query's future accuracy.

 

EDIT: You should compare cnt to the number of $city_id's in the WHERE clause

 

[effigy]

shoz: Beautiful.

[mihail]

Aaaauch! That is mind blowing, guys!!! But it works! Thanks!

[fenway]

shoz: Beautiful.

Sorry, I misread the question... but yes, very elegant; I've seen similar problems solved with self joins, too.