Web Analytics Made Easy -
StatCounter another join problem :( - CodingForum

Announcement

Collapse
No announcement yet.

another join problem :(

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    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

    Comment


    • #3
      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
      Be yourself. No one else is as qualified.

      Comment


      • #4
        wow i was over doing it! dunno where i would be without you mate!

        appreciate it!!!!

        Comment


        • #5
          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

          Comment


          • #6
            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.
            Be yourself. No one else is as qualified.

            Comment


            • #7
              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!!!!!

              Comment

              Working...
              X