right outer join - please Help!

[fiona]

I have two tables m & t (master/transactions) which are basically a customer and his/her transactions. I need to get all the master details and his/her transactions. I need the master details even if the customer has no transactions. The following statement achieves this for customer 'N06'.

select m.code, m.cstname, t.trdate from m right outer join t on accno=code where code='N06'

I want to take this further and add a condition on transaction date.

This is where I got stuck. The following statement does not bring up the master details if there are no transactions after 2005-01-01.

select m.code, m.cstname, t.trdate from m right outer join t on accno=code where code='N06' and trdate > '2005-01-01'

I can understand why this happens but I haven't got a clue how to write a statement to achive my goal. Any suggestions would be greatly appreciated. Thanks.

[skhanal]

Try putting your date clause in ON clause.

[fiona]

I already tried that but it gave me a syntax error on the greater than sign.
Just for kicks I tried = and it worked.
It seems that ON works only with =.

[skhanal]

How about >=

[fiona]

Same thing :-(

[MAK]

select m.code, m.cstname, t.trdate from m right outer join
(select * from t where code='N06' and trdate > '2005-01-01') as mytable
on accno=code

[fiona]

Mak thanks for your reply.

Unfortunately I got a syntax error inside the subquery. There are however a couple of things I cannot understand in your suggestion. They may be related.

1) "right outer join" expects a table name. The subquery will get it a resultset of transactions from the subquery. It is not what the join command is expecting no?

2) what does "as mytable" achieve in this case?

Thanks again.

[Rawhide]

The reason why MAK's solution works and yours doesn't is that by adding the criteria on the "outer" table, you have basically changed the join to an inner join. If there are no transactions for the user, then obviously trdate will not be greater than '2005-01-01'. In fact, trdate will be null.

Another way to resolve this is to allow the field to be null or greater than 2005-01-01. If you do it this way, make sure that the field that you are checking for null is a field that is 100% populated or the results will be tainted. If you're not sure that a field is 100% populated, play it safe and use MAK's solution!!!

select m.code, m.cstname, t.trdate
from m
right outer join t on accno=code
where code='N06'
and (trdate > '2005-01-01' Or accno is null)

[Rawhide]

Originally posted by fiona
Unfortunately I got a syntax error inside the subquery.

There are no errors in MAK's posted code. Double check your code and post it here if you still get an error.

Originally posted by fiona
1) "right outer join" expects a table name. The subquery will get it a resultset of transactions from the subquery. It is not what the join command is expecting no?

MAK has created what is called a "Dynamic Table". It's not supported by all database systems. So if you are using, for example, MS Access or SQL Server 7.0, it won't understand the code.

Originally posted by fiona
2) what does "as mytable" achieve in this case?

This is called "aliasing". You can alias tables, columns, etc. When you use a dynamic table, an alias is REQUIRED. So basically, MAK was giving the subquery (dynamic table) a name.

[fiona]

rawhide thanks for your input too. I appreciate it.

I am using connx JDBC and it looks like it does not support dynamic tables.

As for your solution ...
It worked fine for accounts that have no transactions whatsoever but in the case of accounts that do have transactions but none after 2005-01-01 the same thing happened and that is the master details were not brought up.

[Rawhide]

Then do the two options separately and Union them.

select m.code, m.cstname, t.trdate
from m
right outer join t on accno=code
where code='N06'
and trdate > '2005-01-01'
Union
select m.code, m.cstname, t.trdate
from m
right outer join t on accno=code
where code='N06'
and accno is null

[fiona]

That didn't work either but ....

When I changed the last line

and accno is null

to

and trdate is null

It worked!!!! While I'm happy, I'm a bit
uneasy because I haven't got the faintest idea why this happened!

If both accno & trdate are on the same row shouldn't the validation apply equally well for both?

[Rawhide]

Just remember that if the column allows nulls, then there could be instances where the column is null even though there are transactions.

[fiona]

Yes of course.

But this is not the case here. I checked and there are no null trdate in all the table.

I also noticed that when I use "trdate is null" instead of "accno is null", an extra row in the resultSet is brought up containing the master details and blank transaction details.