What JOIN do I use??

[beckyh]

I would like to write a query joining two tables by UnitID and NatureID. Is there a JOIN I can use that will return all rows from table1 and all rows from table 2 where they meet the criteria? As well as join where there is a match, and return rows where there isn't a match?

 

[PHV]

1) All rows from table1, only matching rows from table2
FROM table1 LEFT OUTER JOIN table2 ON ...
2) Only matching rows
FROM table1 INNER JOIN table2 ON ...
3) Only matching rows from table1, all rows from table2
FROM table1 RIGHT OUTER JOIN table2 ON ...
4) All rows
FROM table1 FULL OUTER JOIN table2 ON ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[beckyh]

If this is table 1 rows....
UNIT Nature Month Amt
6121 515000 APR05 46
6121 544002 APR05 65
6121 554006 APR05 299
6121 714000 APR05 9848
6121 733500 APR05 267
6121 734000 APR05 10196
6121 734500 APR05 40010

and this is table 2 rows...

Unit Nature Month Amt2
6121 515000 APR05 582
6121 544002 APR05 32783
6121 554006 APR05 166363
6121 714000 APR05 394583
6121 732500 APR05 368910
6121 732502 APR05 24969
6121 734500 APR05 2815155

I would like the query to return these 9 rows....

Unit Nature Amt2 Amt
6121 515000 582 46
6121 544002 32783 65
6121 554006 166363 299
6121 714000 394583 9848
6121 734500 2815155 40010
6121 732502 24969
6121 732500 368910
6121 733500 267
6121 734000 10196

 

[JamesLean]

I think you want this:

SELECT COALESCE(t1.unit, t2.unit) AS unit,
  COALESCE(t1.nature, t2.nature) AS nature,
  t2.amt2,
  t1.amt
FRMO table1 t1
  FULL JOIN table2 t2 ON t1.unit = t2.unit
    AND t1.nature = t2.nature
ORDER BY COALESCE(t1.unit, t2.unit),
  COALESCE(t1.nature, t2.nature)

--James

 

[PHV]

Another way if your RDBMS chokes on full outer joins:
SELECT t1.unit, t1.nature, t2.amt2, t1.amt
FROM table1 t1 LEFT JOIN table2 t2
ON t1.unit = t2.unit AND t1.nature = t2.nature
UNION
SELECT t2.unit, t2.nature, t2.amt2, t1.amt
FROM table1 t1 RIGHT JOIN table2 t2
ON t1.unit = t2.unit AND t1.nature = t2.nature
WHERE t1.unit Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[beckyh]

James - I am getting syntax errors somewhere in the FROM clause I believe. I did fix the spelling of FROM, so that's not it. You have

FRMO table1 t1

Isn't table1 the same as t1??

 

[JamesLean]

t1 is an alias for table1. Try this:

FROM table1 AS t1
  FULL JOIN table2 AS t2 ON ...

BTW, what DBMS are you using?

--James

 

[beckyh]

PHV or James - I don't think Access like the full outer join, as well as SQL Server. What kind of program accepts this syntax??

 

[PHV]

beckyh, just to know, have you tried my suggestion ?

 

[beckyh]

I am using Access and SQL Server both to test these sql statements.

PHV- I am trying both SQL statements to see which one runs more efficiently, or without errors!

I am adding in more criteria right now. Eventually I will be running this SQl through a Cold Fusion webpage.

More in a few......

Thanks for the wonderful suggestions....I am moving right along now with new ideas

 

[JamesLean]

The full join should work fine on SQL Server. As you say, I'm not sure that Access supports it.

FYI, this forum is for ANSI-standard SQL. There are many differences between the flavours of SQL used in SQL Server and Access so you may be better off posting in forums specific to each one.

--James