Advanced mysql select query

[successlieswithme]

Hello,
i need a smart query

i have a 3 tables stores, cats and relcats

in stores, i keep all stores info
in cats, i keep all cats info
in relcats, i choose related categories for each stores.

with these tables, i show related categories for each store.

now i want to show related stores for each store using relcats table.

ex:for Dell, i choose related categories as PCs, Notebooks.
and we know: HP, sony are related stores for it ( i.e. i choose PCs, notebooks as related categories for both HP and Sony)

so now when i access Dell page,
i want to show HP, Sony under related stores.

please help me with this

thanks a bunch in advance.

[mack]

I think you may need to create a table for related stores in much the same way you had to create the relcats table. Without having this info in the databaase it wont be possible to have a script gather the information to display.

Mack.

[DWarp9]

Suppose the stores table contains columns store_id and store_name, and that table relcats contain columns store_id and cat_id, this query should do the trick (@StoreID would be the id of Dell in your example):

Select a.store_name From stores a Inner Join
(Select store_id From relcats Where cat_id In
(Select cat_id From relcats Where store_id = @StoreID)) b
On a.store_id = b.store_id;

store_name would then hold the names of all related stores.
Of course, this has not been tested, and performance-wise I am unsure as the IN-operator generally isn't my first choice. But have a look at it, test it through and let me know how it works.