Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,088
    Thanks
    296
    Thanked 12 Times in 12 Posts

    Exclamation another join problem :(

    these inner/outer/left joins are causing me many problems

    i am trying to creat a query which will:

    1/ pull out ll the rows from a table (has 40000+ rows)
    2/ then join the price table BUT only return 1 row from the prices table where the price is the cheapest

    so this query
    Code:
    SELECT *
    FROM `tbl_dvds` AS dvd
    INNER JOIN (
    tbl_prices AS price
    ) ON price.prodID = dvd.filmID
    returns 100,000+ rows because it returns a new row for each price found for every product

    so at the moment say the above query is returning:

    prod1 -> ... -> 3.99
    prod1 -> ... -> 5.99
    prod1 -> ... -> 1.99
    prod2 -> ... -> 6.99
    prod3 -> ... -> 16.99
    prod3 -> ... -> 16.99

    ... show more fields

    i want it to return this
    prod1 -> ... -> 1.99
    prod2 -> ... -> 6.99
    prod3 -> ... -> 16.99

    is this possible?
    many thanks

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,088
    Thanks
    296
    Thanked 12 Times in 12 Posts
    tried this
    Code:
    SELECT *
    FROM `tbl_dvds` AS dvd
    INNER JOIN (
    
    SELECT *
    FROM tbl_prices
    ORDER BY prodPrice ASC
    LIMIT 0 , 1
    ) AS price ON price.prodID = dvd.filmID
    LIMIT 0 , 30
    which returned 1 row but says:
    Showing rows 0 - 29 (275,592 total, Query took 0.2366 sec) in my php myadmin

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Man oh man, are you working too hard.
    Code:
    SELECT dvd.field1, dvd.field2, dvd.field3, MIN(price.prodPrice) AS lowestPrice
    FROM `tbl_dvds` AS dvd, tbl_prices AS price
    WHERE price.prodID = dvd.filmID
    GROUP BY dvd.field1, dvd.field2, dvd.field3
    ORDER BY any-fields-you-want
    You must give the list of fields so that you can repeat the list in the GROUP BY.

    Yes, of course you can use INNER JOIN syntax. The semantics are 100% the same as is the performance.
    Code:
    SELECT dvd.field1, dvd.field2, dvd.field3, MIN(price.prodPrice) AS lowestPrice
    FROM `tbl_dvds` AS dvd INNER JOIN tbl_prices AS price
    ON price.prodID = dvd.filmID
    GROUP BY dvd.field1, dvd.field2, dvd.field3
    ORDER BY any-fields-you-want
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    LJackson (08-30-2011)

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,088
    Thanks
    296
    Thanked 12 Times in 12 Posts
    wow i was over doing it! dunno where i would be without you mate!

    appreciate it!!!!

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,088
    Thanks
    296
    Thanked 12 Times in 12 Posts
    heres another one

    i am trying to pull data from 3 tables
    tbl_dvds
    tbl_popularity
    tbl_format

    but the problem is that the query only returns items if the count of popularity is 1 or greater and doesnt return any results which has 0 count???

    here is my query
    PHP Code:
    SELECT dvd.filmIDdvd.filmNamedvd.filmThumbIMGdvd.filmBindingdvd.filmReleaseDatefilmAgeRatingfilmTrailerfilmRRPformat.prodIDformat.formatIDCOUNTpop.prodID ) AS popularity
                    FROM tbl_dvds 
    AS dvdtbl_popularity AS poptbl_product_format AS format
                    WHERE pop
    .prodID dvd.filmID && format.prodID dvd.filmID
                    GROUP BY dvd
    .filmIDdvd.filmNamedvd.filmThumbIMGdvd.filmBindingdvd.filmReleaseDatefilmAgeRatingfilmTrailerfilmRRP
                    ORDER BY filmName ASC 
    any ideas?
    thanks mate

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Code:
    SELECT dvd.filmID, dvd.filmName, dvd.filmThumbIMG, dvd.filmBinding, dvd.filmReleaseDate, 
           filmAgeRating, filmTrailer, filmRRP, format.prodID, format.formatID, 
           COUNT( pop.prodID ) AS popularity
    FROM tbl_dvds AS dvd 
    INNER JOIN tbl_product_format AS formatON format.prodID = dvd.filmID
    LEFT JOIN  tbl_popularity AS pop ON pop.prodID = dvd.filmID
    GROUP BY dvd.filmID, dvd.filmName, dvd.filmThumbIMG, dvd.filmBinding, dvd.filmReleaseDate, 
             filmAgeRating, filmTrailer, filmRRP, format.prodID, format.formatID
    ORDER BY filmName ASC
    I think it's time you studied up on SQL and learned about OUTER joins, no?

    Note that you need to GROUP BY *all* non-aggregate fields. You had omitted format.prodID, and format.formatID from your GROUP BY.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    LJackson (08-30-2011)

  • #7
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,088
    Thanks
    296
    Thanked 12 Times in 12 Posts
    Quote Originally Posted by Old Pedant View Post
    [code]
    I think it's time you studied up on SQL and learned about OUTER joins, no?
    totally agree with you there! they're such a major part of website development and i need to learn them inside and out asap.

    thanks again for your help!!!!!


  •  

    Posting Permissions

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