Re: Multiple Select Statements

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

(rich.ai@xxxxxxxxxxxxx) writes:
Works like a charm, many thanks all. I'm going to have to some reading
on this full outer join.

How would I modify the Query to include a 3 more dervied tables X3,X4
& X5 as I'm not sure what the ON 1=1 means. Most probably will become
clear once I've done some reading

The "ON 1 = 1" transforms the outer join to a cross join, but with the added
twist that you will always get at least one row, even if any of the queries
return no rows.

I'm afraid that doing some reading may not be sufficient to understand the
resulting query. It's still a very odd and most likely incorrect query.

The main usage for full outer joins is when comparing two data sets that
ideally should be identical for differences. While one-way outer joins
are common, full joins are far less so, and five-join full joins even
lesser. I have found that even as an experienced SQL programmer, it's
easy to get lost when dealing with full joins.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: Can someone help me with multiple "Left Outer Joins"?
    ... > Using three "Left Outer Joins" slows the system down considerably. ... tables have indexes on the columns you join on, I would expect the query ... could hit multiple rows in the latter tables. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Query with left outer join all of a sudden wont work?
    ... If your comparison in the ON clause is between the two tables, ... can you post the whole SQL statement? ... Then someone went to run a query this week that has a left outer ... Has anyone else had a problem with left outer joins all of a ...
    (microsoft.public.access.queries)
  • Re: reserved error -1524 access 2007
    ... If it's a table, then I can't see any reason why this would not work, assuming the field types are correct, i.e.: ... If OT is a query, ... You can try posting the SQL statement here if you wish, ... If you have yes/no fields and outer joins, it might be the result of JET's ...
    (microsoft.public.access.queries)
  • Re: Comparison Query - How to write it!
    ... message saying "The SQL statement could not be executed because it ... create a separate query that performs the first join ... technique I gave you would not result in any outer joins. ...
    (microsoft.public.access.queries)
  • Query Designer and outer joins
    ... Are there any issues with Access 2000 and outer joins in the query ... I'm using Access to report against a SQL Server db, ... and parentheses so I decided to just use the Query Designer, ...
    (microsoft.public.access.queries)