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 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Dec 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    what's wrong with my query!!

    Hi guys

    I faced a problem in this query and I hope to get help on it

    I have 4 tables which are (model, license, pilot, and person)

    Model: mid, name
    License: mid, pid, licenseDate
    Pilot: pid, hireDate
    person: pid, firstName, lastName

    I want to get the names of pilots who are not licensed to fly any airplane

    I tried this query, but it showed me no result!!!

    select model.mid, license.pid, license.mid, license.licenseDate, pilot.pid, pilot.hireDate, person.firstName, person.lastName
    from model, license, pilot, person
    where pilot.pid = license.pid and model.mid = license.mid and pilot.pid = person.pid
    and not exists(SELECT null FROM model WHERE pilot.pid = license.pid);

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    What possible reason is there to include the MODEL and PILOT tables when they will have no role in finding out if a given pid has a LICENSE or not??

    KEEP IT SIMPLE!
    Code:
    SELECT .P.pid, P.firstName, P.lastName
    FROM pilot AS P LEFT JOIN license AS L
    ON P.pid = L.pid
    WHERE L.pid IS NULL
    ORDER BY P.lastName, P.firstName
    (where the ORDER BY is up to you, of course).
    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.

  • #3
    New to the CF scene
    Join Date
    Dec 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your responding ..

    But firstName and lastName are not in the Pilot table. They are in the Person table so your query doesn't work

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Silly me! I just used the wrong table name!

    Code:
    SELECT P.pid, P.firstName, P.lastName
    FROM person AS P LEFT JOIN license AS L
    ON P.pid = L.pid
    WHERE L.pid IS NULL
    ORDER BY P.lastName, P.firstName
    I had even commented there is no reason to have the PILOT table in the query...and then went and used the wrong table anyway.
    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.

  • #5
    New to the CF scene
    Join Date
    Dec 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you are completely right we don't need to use pilot table since we have the same primary key in person table. I should have noticed that. Thank you very much for helping me with this. I really appreciate it.


  •  

    Posting Permissions

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