Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Apr 2013
    Posts
    72

    Question Unanswered: Help with my Query

    Hi Guys

    Hope you can help me out here.

    * See properties managed, with the branch's town, property details, and the number of inspections carried out for these properties.

    See the image: http://s4.postimg.org/fdhr06ebh/query.jpg

    Anyone can help me on this please????
    It requires to use Inner and outer join but have no idea where to start

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First, figure out what information you need... What columns need to end up in your SELECT list.

    Next look at the tables that contain those columns... How are the tables are related? Which table would you start from if you were building the query by hand? Start with that base table, then relate the other tables in the FROM clause. If a table is optional, it needs to be an outer join.

    See how far you can get with those hints. Post the SQL that you can create, and whatever questions you have about that SQL.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2013
    Posts
    72
    yehhhhhhhhh ... I somehow got it working

    Now I want to run another query .. which compares 2 tables [Table1] and [Table2]
    One table has all productNumbers and then the other tables has only 3 of those P.Numbers

    Now.. what type of SQL Statement do I need to use, in order to show only those that are NOT in the second table [Table2] ??

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Select product_id from all_products
    minus
    select product_id from limited_products;
    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.

  5. #5
    Join Date
    Apr 2013
    Posts
    72
    hI

    This is the code I have:

    SELECT PROPERTY.PROPERTYNUM, LEASE.PROPERTYNUM AS PROPERTYNUM1
    FROM PROPERTY
    INNER JOIN LEASE
    ON PROPERTY.PROPERTYNUM = LEASE.PROPERTYNUM
    This tells you that there are 2 tables: Property tb and the Lease tb
    So, how to I implement the SELECT MINUS within the above statement?

    cheers

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Select propertynum from property
    minus
    select propertynum from lease
    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.

  7. #7
    Join Date
    Apr 2013
    Posts
    72
    AnaCedent

    You are repeating yourself :/
    I already asked, I do not know WHERE to implement that statement into my SQL

  8. #8
    Join Date
    Apr 2013
    Posts
    72
    is this what ur saying:

    SELECT PROPERTY.PROPERTYNUM, LEASE.PROPERTYNUM AS PROPERTYNUM1
    FROM PROPERTY
    INNER JOIN LEASE
    ON PROPERTY.PROPERTYNUM = LEASE.PROPERTYNUM
    Select propertynum from property
    minus
    select propertynum from lease

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT property.propertynum, 
           lease.propertynum AS PROPERTYNUM1 
    FROM   property 
           inner join lease 
                   ON property.propertynum = lease.propertynum
    why are both property.propertynum & lease.propertynum AS PROPERTYNUM1 in SELECT clause,
    since only 1 is required since you know they are equal to each other?

    I don't understand what you are trying to do with MINUS.
    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.

  10. #10
    Join Date
    Apr 2013
    Posts
    72
    Because my SQL is much longer it has 2 Inner Joins
    Basically, it combines 3 tables and then it should show
    only the PropertyNum which are NOT in the Lease table

    I PM u the whole code

  11. #11
    Join Date
    Apr 2013
    Posts
    72
    .... not getting far here

  12. #12
    Join Date
    Apr 2013
    Posts
    72
    This is how far I got

    http://s21.postimg.org/yvpppw2qf/Query3.jpg

    SELECT YR_PROPERTY.PROPERTYNUM,
    YR_PROPERTY.STREET,
    YR_PROPERTY.ROOMS,
    YR_PROPERTY.PROPTYPE,
    YR_PROPERTY.POSTCODE,
    YR_PROPERTY.CITY,
    YR_PROPERTY.AREA,
    YR_LEASE.LEASENUM,
    YR_LEASE.PROPERTYNUM AS Lease_PropertyNum,
    YR_LEASE.RENTSTART,
    YR_LEASE.RENTFINISH
    FROM YR_PROPERTY
    INNER JOIN YR_LEASE
    ON YR_PROPERTY.PROPERTYNUM = YR_LEASE.PROPERTYNUM

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >only the PropertyNum which are NOT in the Lease table
    If you want properties that are not leased, then why join against LEASE table?
    (Since no data will exist.)
    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.

  14. #14
    Join Date
    Apr 2013
    Posts
    72
    AnaCent

    Because, I have NO CLUE what I am doing
    I am using a QUERY TOOL to bundle tables together


    Your code:

    Select propertynum from property
    minus
    select propertynum from Lease
    This works fine and shows extaly the ProductNum that are not in Lease
    but I also want to add an extra colum which shows when it was last adversites,
    and the advertiseDate is in Advert table.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT *
       FROM table1
       WHERE NOT EXISTS (SELECT *
          FROM table2
          WHERE  table2.PropertyNumber = table1.PropertyNumber)
    or
    Code:
    SELECT table1.*
       FROM table1
       LEFT JOIN table2
          ON (table2.PropertyNumber = table1.PropertyNumber)
       WHERE  table2.PropertyNumber IS NULL
    or
    Code:
    SELECT PropertyNumber FROM table1
       MINUS SELECT PropertyNumber FROM table2
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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