Hi Friends,
This is my first post,I hope you guys would help me out.I have a query and a small question.I don't know why the "and/or" part in the query is not working.When I make it work separately,it works,but together it doesnt works.Please help me if you can cause this is really important for me,thanks.

select
tr.tournament_id,tr.tournament_datetime,t.tourname nt_name,ft.transaction_type,ftd.fee_currency,
ftd.fee,ftpd.currency,ftpd.amount

from
finalteam as ft,
final_team_details as ftd,
final_player_details as ftpd,
team_results as tr,
team as t

where
tr.tournament_id=t.tournament_id

and ((ft.transaction_id = ftd.transaction_id and ftd.transaction_date=ft.transaction_date)
or (ft.transaction_id = ftpd.transaction_id and ft.transaction_date=ftpd.transaction_date))

I don't really know what you are trying to do, but since you have 5 tables, you need to join all the tables All the join predicates should be "ANDed" and not just with an "OR". Of course, as I said, I really don't know what you are trying to achieve.

thanks marcus,
I didnt posted the whole query for simplicity,here goes the full query:

select
tr.tournament_id,tr.tournament_datetime,t.tourname nt_name,ft.transaction_type,ftd.fee_currency,
sum(ftd.fee) as FEES,ftpd.currency,sum(ftpd.amount) as MONEYPAYED

from
finalteam as ft,
final_team_details as ftd,
final_player_details as ftpd,
team_results as tr,
team as t


where
(
(
tr.tournament_id=t.tournament_id
and tr.status='co'
and tr.tournament_id=ftd.tournament_id
and ft.transaction_status='finalize'
and ftd.fee_currency is not null
and ft.transaction_type in ('group1','group2')
)

and
(
(
ft.transaction_id = ftd.transaction_id
and ftd.transaction_date=ft.transaction_date)
or (ft.transaction_id = ftpd.transaction_id
and ftpd.transaction_date=ft.transaction_date
)
)
)



group by
tr.tournament_id,
tr.tournament_datetime,
t.tournament_name,
ft.transaction_type,
ftd.fee_currency,
ftpd.currency


Actually,I want that if transaction_type is in 'group1' then it should pick up details from
final_team_details matching on transaction_id and transaction_date or else if type is in 'group2'
it should pick details from final_player_details same matching criteria as before.
Now can you help me,it's really really important for me.

Don't miss to analyze the query using Visual Explain .. I have come across circumstances when the db2 optimizer is 'confused' when you give too many join conditions .. Of course, this depends on the size of the tables, cardinality, data distribution etc, but just in case it helps

Cheers
Sathyaram