StormPooper StormPooper - 1 year ago 71
SQL Question

SQL oddity when using 2 tables in a query

A colleague was creating a query for two tables he has set up - UK Time and Egypt Time. The SQL for his query was as so:

SELECT Sum([UK Time].[Hours Booked]), [UK Time].[First Name], [UK Time].[Last Name], [UK Time].[Month]

FROM [UK Time], [Egypt Time]
GROUP BY [UK Time].[First Name], [UK Time].[Last Name], [UK Time].[Month]
HAVING (([UK Time].[First Name])="Geoff"), ([UK Time].[Month])="September 2009"));

UK Time had 18 results that matched both 'Geoff' and 'September 2009', and Egypt Time had a total of 6022 entries (UK Time had a similar total number).

When running this query, it would return the 18 results, but duplicate them 6022 times each, giving a total of 108396 entries in the query. When Egypt Time was removed from the query it only showed the 18 results.

My colleague has simply created two queries now, one per table, but I was hoping somebody on here would be able to explain this behavior.

Answer Source

To expand on Kevin's answer:

A CARTESIAN or FULL OUTER JOIN basically gives a product of the tables, with a row in the result set for every row in the left table matched up with every row in the right table.

This has it's uses but they are rare.

You want to do something like

FROM LeftTable
INNER JOIN RightTable ON LeftTable.Key = RightTable.Key

This will give you only records that match up between the two tables.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download