Thread: Two table query

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0

    Two table query


    Hi all

    I was wondering if someone can help me with my query.

    This is my query that works great:

    PHP Code:
    $query sprintf("SELECT primary_key, address, postcode, lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM stores HAVING distance < '%s' ORDER BY distance LIMIT 0 , 5" 
    I now need to retrieve data from a table called products; the primary key, column1 and column2.
    The primary key being the common field.

    How would I add to this query?
    Hope someone can help me?

    Many thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by pc_mac
    The primary key being the common field.
    almost, but not quite

    i'm guessing from your stores table name, that there is going to be one row per store

    so the primary key of the stores table will be a store number or store id or something like that

    the products table, however, would have one row per product

    can you see why primary_key isn't a "common field" for the join?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by r937
    almost, but not quite

    i'm guessing from your stores table name, that there is going to be one row per store

    so the primary key of the stores table will be a store number or store id or something like that

    the products table, however, would have one row per product

    can you see why primary_key isn't a "common field" for the join?
    I see what you're saying. This database was given to me.
    The stores table is basically the address and an id. The product table has the the product with its own id and a column with the store id the products are stocked in. Will the join not work this way? What would you suggest? Should I create another table thats stores the id for the store and product?
    Store-1 can stock product-1, product-2, product-4.
    Store-2 can stock product-5, product-2, product-3.
    Store-3 can stock product-5, product-1, product-3. etc...

    Thanks
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by pc_mac
    Should I create another table thats stores the id for the store and product?
    absolutely, yes

    if the product has a store id in its row, that implies that a given product can be in only one store, a one-to-many relationship (a store has many products but a product is in only one store)

    what you want is a many-to-many relationship
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by r937
    absolutely, yes

    if the product has a store id in its row, that implies that a given product can be in only one store, a one-to-many relationship (a store has many products but a product is in only one store)

    what you want is a many-to-many relationship
    Ok, yep makes sense! I'll do that.
    What about the query? any guidance on that?

    At the moment, I have this:

    PHP Code:
    SELECT stores.primary_keyproducts.primary_keystores.addressstores.postcodestores.latstores.lngproducts.column1products.column2, ( 3959 acoscosradians('%s') ) * cosradianslat ) ) * cosradianslng ) - radians('%s') ) + sinradians('%s') ) * sinradianslat ) ) ) ) AS distance FROM stores INNER JOIN products ON products.primary_key stores.primary_key HAVING distance '%s' ORDER BY distance LIMIT 0 10 
    This works but the store is duplicated with the amount of products that store holds. I want the store name to display once with the list of products. Currently it display the store, with product 1; display the same store again with product 2... etc...if that makes sense?!

    Would this query work same when I change the table name to the new one with the id's?

    Thanks again for your help.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by pc_mac
    At the moment, I have this:

    Code:
    ... FROM stores INNER JOIN products ON products.primary_key = stores.primary_key
    i still don't think that's working the way it should, or else possibly the product's primary_key column is actually the store_id

    Originally Posted by pc_mac
    This works but the store is duplicated with the amount of products that store holds.
    that's going to be the case when you fix your tables and query to the many-to-many model too

    you "suppress" the repeated store information using your application language (php or whatever) when printing the data
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by r937
    i still don't think that's working the way it should, or else possibly the product's primary_key column is actually the store_id

    that's going to be the case when you fix your tables and query to the many-to-many model too

    you "suppress" the repeated store information using your application language (php or whatever) when printing the data
    Yes that primary key is the store id...

    I am using PHP. This is a store locator, the closest stores are outputted in an XML format. I'll have a look around for any tutorials to stop the repeated information. Thanks for your help again, much appreciated.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by pc_mac
    Yes that primary key is the store id...
    could you do me a favour and run this please and post the results --
    Code:
    SHOW CREATE TABLE products
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo