Welcome to WebmasterWorld Guest from 3.234.210.89

Forum Moderators: open

Message Too Old, No Replies

Advanced mysql select query

     
9:54 am on Jan 24, 2009 (gmt 0)

Junior Member

10+ Year Member

joined:June 5, 2005
posts:155
votes: 0


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.

12:15 am on Jan 25, 2009 (gmt 0)

Moderator from GB 

WebmasterWorld Administrator mack is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:June 15, 2001
posts:7848
votes: 96


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.

10:12 pm on Mar 3, 2009 (gmt 0)

New User

10+ Year Member

joined:May 17, 2006
posts: 24
votes: 0


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.