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

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

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?

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.