6 Replies Latest reply: Apr 3, 2012 4:42 PM by Woyswer RSS

    Help required with SQL Query

    Woyswer
      I have three tables as listed below

      FORMAT -- Columns are format_id, format_desc
      VIDEO_FORMAT -- columns areVIDEO_FORMAT_ID, VIDEO_ID, FORMAT_ID,COST
      RENTAL -- Coulmns are RENTAL_ID, DATE_OUT, DATE_DUE, DATE_IN, DELIVERY_STATUS, LATE_FEE, CUSTOMER_ID, VIDEO_FORMAT_ID

      I need to find List customer id, format description and cost of video format for those customers who rented a video.

      I wrote the query

      Select r.customer_id, f.format_desc, vf.cost from rental r, format f,video_format vf where f.format_id in (select format_id from video_format) and r.video_format_id = vf.video_format_id

      this is not fetching exact results. Please help.
        • 1. Re: Help required with SQL Query
          WannaRock
          Would help to know what kind of results you're getting but I think I can see.

          Try:
          Select r.customer_id, f.format_desc, vf.cost
          from rental r, format f,video_format vf
          where vf.format_id = f.format_id
          and r.video_format_id = vf.video_format_id

          Can't be sure without a bit more info if that will fix you. But I'm not sure why you went with trying to get a relationship using an IN with a subquery. Hope it helps.
          • 2. Re: Help required with SQL Query
            Woyswer
            Current values in table (Sorry abt thr formatting)

            FORMAT
            FORMAT_ID
            (primary key, surrogate key
            FORMAT_DESC
            1
            VCR
            2
            DVD
            3
            Playstation
            4
            Nintendo 64
            5
            Sega


            VIDEO_FORMAT
            VIDEO_FORMAT_ID
            (primary key, surrogate key
            VIDEO_ID
            (foreign key)
            FORMAT_ID
            (foreign key)
            COST
            1
            1
            1
            $2.00
            2
            1
            2
            $3.00
            3
            2
            2
            $1.00
            4
            3
            4
            $2.00
            5
            4
            1
            $2.00
            6
            4
            2
            $3.00
            7
            5
            1
            $2.00



            RENTAL
            RENTAL_ID
            (primary key, surrogate key
            DATE_OUT
            DATE_DUE
            DATE_IN
            DELIVERY_STATUS
            LATE_FEE
            CUSTOMER_ID
            (foreign key)
            VIDEO_FORMAT_ID
            (foreign key)
            1
            2011-09-22
            2011-09-24
            2011-09-24
            pickup
            $2.00
            1
            1
            2
            2011-09-23
            2011-09-25
            delivery
            $3.00
            3
            2
            3
            2011-09-25
            2011-09-27
            2011-09-26
            pickup
            $2.00
            1
            2
            4
            2011-09-25
            2011-09-27
            2011-09-26
            pickup
            $2.00
            2
            2
            5
            2011-09-27
            2011-09-28
            delivery
            $1.00
            5
            4
            • 3. Re: Help required with SQL Query
              WannaRock
              Before I try to decipher that, did you try the query I posted above? :) If that didn't help, I'll take a look at that data you pasted and see where I went wrong.
              • 4. Re: Help required with SQL Query
                Woyswer
                Sorry.. its not fetching the correct records.
                • 5. Re: Help required with SQL Query
                  Woyswer
                  Hi.. looks like your query is working... Thanks..

                  There was some data issue in the table, after correcting it, the results are fine.

                  WannaRock -- You really rock.

                  Thanks Again.
                  • 6. Re: Help required with SQL Query
                    WannaRock
                    Glad it helped. :)