Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Question Unanswered: A complicated query

    Hi

    Im trying to create a rather complicated query and Im looking for asistance.

    I have 2 tables A-B-C and I want to get all records from A that are related with B-C where a condition is met in C AND all of those that are not included in B.

    I have two queries like this:

    a) first query
    SELECT *
    FROM A, B, C
    WHERE B.A_ID = A.ID
    AND B.C_ID = C.ID
    AND C.c_date > '2009-05-01 23:59:59'

    b) second query
    SELECT *
    FROM A
    WHERE A.ID NOT IN (SELECT DISTINCT B.A_ID)


    I would like to mix both queries and I dont know how.
    I have tried UNION but table A has some float fields and when I do the join that column goes crazy with decimal values:
    0.3 appears as 0.300000011920929

    I gues a left Join of shorts could work... but I cant figure it out.

    Any help?


    Thanks!
    Gonso

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    she's no possible

    in the first query, you want A to match B --

    ... WHERE B.A_ID = A.ID

    in the second query, you want A ~not~ to match B --

    ... WHERE A.ID NOT IN (SELECT DISTINCT B.A_ID)

    therefore these queries cannot be combined

    or else, if they are combined, it will never return any rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Posts
    2
    Quote Originally Posted by r937
    she's no possible
    It is possible, just like this:
    SELECT *
    FROM A, B, C
    WHERE B.A_ID = A.ID
    AND B.C_ID = C.ID
    AND C.c_date > '2009-05-01 23:59:59'
    UNION
    SELECT *
    FROM A
    WHERE A.ID NOT IN (SELECT DISTINCT B.A_ID)

    This returns the right set of records from A (its an kind of OR), but with float values going crazy.

    Notice that the FK goes from B to A (not all the way around).

    Im asking if there is another way, or a fix for the float problem....

    help?

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    The float problem is caused by the fact that computers store numbers as binary and some numbers (ie 1/3) simply don't store exactly in binary - see this article for help.

Posting Permissions

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