Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2010
    Location
    London, UK
    Posts
    7

    Unanswered: Problems with Oracle SQL syntax - pls help !!

    Hi guys,

    As a newbie in this game, I have a typical rookie question, hope its not too stupid. Spent many hours trying to solve it myself, lost hope though - just do not know enough...
    I have a query
    which gives me information about all vehicles that were rented out. To this list I need to add now list of vehicles that were never rented out. I can get this using this query

    SELECT vModel
    FROM vehicle
    WHERE vehicle.vModel NOT
    IN (

    SELECT DISTINCT vehicle.vModel
    FROM rental
    LEFT JOIN vehicle
    USING ( VehicleID )
    )
    [/code]

    my question is, how (if possible at all) can I display result of my 2nd query as a last column of the list given by 1st query.

    any help or hints will be greatly appreciated.

    mike
    Last edited by szuwarencja; 11-29-10 at 11:52.

  2. #2
    Join Date
    Nov 2010
    Location
    Pensacola, FL
    Posts
    5
    It isn’t clear what table some of the fields are coming from, so I will guess. It looks like tariff_group is not being used; it may be creating additional undesirable records. Your first query is using the old join syntax, but the second is using the new join syntax which is preferred. To get vehicles that were never rented out you need to left join the rental table and all tables that would eliminate a vehicle because it is not rented. Here is what I think your query might need to look like.

    Code:
    SELECT DISTINCT d.cName AS Country, a.bCity AS City, b.vModel, g.eName, 
       f.startDate AS RentalStart, f.endDate AS RentalEnd, 
       g.eprice + e.tariff as TotalCost
    FROM vehicle b 
    LEFT JOIN rental f ON f.vehicleID = b.vehicleID 
    LEFT JOIN branch a ON f.branchID = a.branchID
    LEFT JOIN country d ON a.countryID = d.countryID
    LEFT JOIN tariff e ON b.tGroupID = e.tGroupID AND a.countryID = e.countryID
    LEFT JOIN rental_all_item h ON f.rentalID = h.rentalID
    LEFT JOIN rental_extra_item g ON g.extraID = h.extraID
    Last edited by Leigh Riffel; 11-29-10 at 10:55.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >how (if possible at all) can I display result of my 2nd query as a last column of the list given by 1st query.
    What is to happen if second query returns fewer rows than the first query?
    What is to happen if second query returns more rows than the first query?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Nov 2010
    Location
    Pensacola, FL
    Posts
    5
    The first query returns everything rented and the second returns everything not rented. My query returns everything rented and not rented with the entries that are rented including a startDate and endDate and the entries that are not returning NULL for those columns.

    If you really need a new column at the end you could add
    Code:
    , NVL2(f.startDate,NULL,b.vModel)
    This would show the vModel when there are no rentals and NULL when there is a rental.
    Last edited by Leigh Riffel; 11-29-10 at 10:56.

  5. #5
    Join Date
    Nov 2010
    Location
    London, UK
    Posts
    7

    Thumbs up

    thanks for your fast replies guys !!

    Leigh Riffel - I'm well impresed with your query, works perfectly. now really need to get to know various joins! in your second reply you have added interesting line NVL2(f.startDate,NULL,b.vModel). What is this NVL2 ?
    Also, after adding above line now I have models that were never rented displayed at the bottom of the ones that were rented and again in new column. Is there a way to have them only in the new column ? How can I add condition so in vmodel column only the rented ones will be displayed ?

  6. #6
    Join Date
    Nov 2010
    Location
    Pensacola, FL
    Posts
    5
    Here is the SQL Language Reference documentation for NVL2. It examines the first parameter and if it is not null returns the second parameter and if it is null returns the third.

    You can re-use the NVL2 to only show rented models for the first vModel entry by replacing b.vModel with the same NVL2 command I listed earlier only switching the second and third parameters.

  7. #7
    Join Date
    Nov 2010
    Location
    London, UK
    Posts
    7

    Thumbs up

    really appreciate your help and knowledge !! i'm suprised that our module notes (Oracle edition for students) does not even mention this NVL2.
    Great stuff, now need more coffee and going to experiment with the stuff that you have showed me !!
    thanks again !

Posting Permissions

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