when selecting from multiple tables I get a multiplication of the records

Question

When executing this query below it takes too long. And the result is too much records, more than possibly in the database. The query should return around 117.000 records. That is the amount of records enterred into the database during a period of half a year.

Why is this query multiplying, resulting in millions of records? If someone could give me the clu where I'm stuck, I'll be very happy.

/* Formatted on 2011/08/05 17:09 (Formatter Plus v4.8.7) */
SELECT   t2.flag, t3.reg_number, t1.reportdate, t1.latitude, t1.longitude,
     t1.value2 course, ((t1.measspeed) * 3.6) / 1.852, t1.status alarms
FROM traffic t1, details t2, registernumbers t3
WHERE     t1.mobileno = t2.mobile
     AND t2.mobile = t3.mobileno
     AND UPPER (t2.flag) = UPPER ('BEL')
     AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
     AND (latitude > 52 AND longitude > 2)
     AND latitude > 48
  OR (latitude < 52 AND longitude > 2)
  OR (latitude > 52 AND longitude < 2)
ORDER BY reportdate DESC

Thanks in advance,

Maarten

Answer 1

The part of the filter that performs the join is t1.mobileno = t2.mobile AND t2.mobile = t3.mobileno. For that to be sensible, it is likely that you need two of the following three statements to be true:

1. mobileno is unique in traffic
2. mobile is unique in details
3. mobileno is unique in registernumbers

Because the join is returning more rows than you expect, I guess this is not the case. For further help we'll need to know the DDL for the tables (in particular the primary keys)

---EDIT

on second thoughts, I think

WHERE     t1.mobileno = t2.mobile
     AND t2.mobile = t3.mobileno
     AND UPPER (t2.flag) = UPPER ('BEL')
     AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
     AND (latitude > 52 AND longitude > 2)
     AND latitude > 48
  OR (latitude < 52 AND longitude > 2)
  OR (latitude > 52 AND longitude < 2)

needs to have some more brackets:

WHERE     t1.mobileno = t2.mobile
     AND t2.mobile = t3.mobileno
     AND UPPER (t2.flag) = UPPER ('BEL')
     AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
     AND (latitude > 52 AND longitude > 2)
     AND ( latitude > 48 
           OR (latitude < 52 AND longitude > 2) 
           OR (latitude > 52 AND longitude < 2) )

although some of the conditions seems to be redundant

Answer 2

Your WHERE clause is poorly formed for your needs. AND and OR operations have equal precedence in evaluation, so with the clause you've listed:

 WHERE     
 t1.mobileno = t2.mobile
 AND t2.mobile = t3.mobileno
 AND UPPER (t2.flag) = UPPER ('BEL')
 AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
 AND (latitude > 52 AND longitude > 2)
 AND latitude > 48
 OR (latitude < 52 AND longitude > 2)
 OR (latitude > 52 AND longitude < 2)

you've got 3 conditions that are being OR'd together... a record is selected if any of the following is true (I listed them in reverse order to make the issue clearer):

• its latitude and longitude meet one criterion
• its latitude and longitude meet another criterion
• its mobileno, flag, reportdate, latitude, and longitude all meet other conditions.

You need to add more parentheses in order to force the OR conditions to be evaluated within the context of a single AND condition, rather than having the ORs all stand on their own:

 WHERE     
 t1.mobileno = t2.mobile
 AND t2.mobile = t3.mobileno
 AND UPPER (t2.flag) = UPPER ('BEL')
 AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
 AND (latitude > 52 AND longitude > 2)
 AND ( 
         latitude > 48
     OR (latitude < 52 AND longitude > 2)
     OR (latitude > 52 AND longitude < 2)
 )

Answer 3

You should consider using the more modern INNER JOIN syntax. Using table1, table2, table3 often leads to cartesian product. It's still possible with table1 INNER JOIN table2 ON ... INNER JOIN table3 ON ... however it is far less likely when you logically separate the join criteria from the filter criteria.

While the following post deals with SQL Server specifically, the concept is essentially the same:

• Bad habits to kick : using old-style JOINs