SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Advanced mysql select query

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT this_store.id
         , this_store.name
         , that_store.id
         , that_store.name
      FROM stores AS this_store
    INNER
      JOIN relcats AS rel_this
        ON rel_this.store_id = this_store.id
    INNER
      JOIN relcats AS rel_that
        ON rel_that.cat_id = rel_this.cat_id
       AND rel_that.store_id <> rel_this.store_id
    INNER
      JOIN stores AS that_store
        ON that_store.id = rel_that.store_id
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,
    very much thanks for response,

    can you please tell me what does it returns?

    basically, i need only store id, because i have other code which displays store name, when we pass store id.

    thanks.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you didn't test it?

    it returns the id and name of every store, and the id and name of every store related to each store

    just like you wanted
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,
    i have tried it but its giving me different output,

    what i want to get from is,
    id of related stores for a particular store ( we pass id of that store).

    please give me code for this.....
    iam really grateful to you.

    thanks.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by successlieswithm View Post
    what i want to get from is,
    id of related stores for a particular store ( we pass id of that store).
    just add a WHERE clause...
    Code:
    SELECT that_store.id
         , that_store.name
      FROM stores AS this_store
    INNER
      JOIN relcats AS rel_this
        ON rel_this.store_id = this_store.id
    INNER
      JOIN relcats AS rel_that
        ON rel_that.cat_id = rel_this.cat_id
       AND rel_that.store_id <> rel_this.store_id
    INNER
      JOIN stores AS that_store
        ON that_store.id = rel_that.store_id
     WHERE this.store_id = 937
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •