RIAS
asked on
SQL query with join
Hi,
Any dvise on how to create joins in the query below will be appreciated
Select * From Personal_Details
Where
((Personal_Details.CURRSta tus in ('Active','PreservedPensio ner','Pens ioner','Wi dow','Chil d'))
OR (Personal_Details .CURRStatus = 'No Liability')
)
and (Personal_Details .Dateleft > dateadd(y,9-20-2011,-1)
or DEATHBENEFIT Dbdate > dateadd(y,9-20-2011,-1)
or PENSIONER_DETAIL.Pdpencomm > dateadd(y,9-20-2011,-1)
or TRANSFER_OUT_HISTORY > dateadd(y,9-20-2011,-1))
Cheers
Any dvise on how to create joins in the query below will be appreciated
Select * From Personal_Details
Where
((Personal_Details.CURRSta
OR (Personal_Details .CURRStatus = 'No Liability')
)
and (Personal_Details .Dateleft > dateadd(y,9-20-2011,-1)
or DEATHBENEFIT Dbdate > dateadd(y,9-20-2011,-1)
or PENSIONER_DETAIL.Pdpencomm
or TRANSFER_OUT_HISTORY > dateadd(y,9-20-2011,-1))
Cheers
ASKER
Hi,
Thanks for the reply.
Is it possible for you to restructure the query.I am not very good in sql.
Many Thanks
Thanks for the reply.
Is it possible for you to restructure the query.I am not very good in sql.
Many Thanks
ASKER
Assume there will be more joins required.
Select * From Personal_Details
Where
((Personal_Details.CURRSta tus in ('Active','PreservedPensio ner','Pens ioner','Wi dow','Chil d','No Liability'))
and (Personal_Details .Dateleft > dateadd(y,9-20-2011,-1)))
or DEATHBENEFIT Dbdate > dateadd(y,9-20-2011,-1)
or PENSIONER_DETAIL.Pdpencomm > dateadd(y,9-20-2011,-1)
or TRANSFER_OUT_HISTORY > dateadd(y,9-20-2011,-1)
Where
((Personal_Details.CURRSta
and (Personal_Details .Dateleft > dateadd(y,9-20-2011,-1)))
or DEATHBENEFIT Dbdate > dateadd(y,9-20-2011,-1)
or PENSIONER_DETAIL.Pdpencomm
or TRANSFER_OUT_HISTORY > dateadd(y,9-20-2011,-1)
Join to what? Please rephrase your question so that we know what you are trying to accomplish.
Also, when you combine "or" and "and" in the where clause, please use parenthesis to explicitly express the order of operations.
Also, when you combine "or" and "and" in the where clause, please use parenthesis to explicitly express the order of operations.
A "join" is a way to combine rows from two tables. While you can join a table to itself, I don't think that is what you have in mind. For us to illustrate a join, we need to know what other tables are involved and what columns form the relationship between them.
Here's an example of joining PersonalDetails to (hypothetical) Employee:
Select PersonalDetails.*, Employee.Name
From Personal_Details
inner join Employee on Personal_Details.EmployeeI D = Employee.EmployeeID
Here's an example of joining PersonalDetails to (hypothetical) Employee:
Select PersonalDetails.*, Employee.Name
From Personal_Details
inner join Employee on Personal_Details.EmployeeI
I dont think you require a join statement here as you are referring only to one table.
you can just combine CURRStatus in one instead of an "OR"
Select * From Personal_Details
Where
((Personal_Details.CURRSta tus in ('Active','PreservedPensio ner','Pens ioner','Wi dow','Chil d','No Liability'))
and (Personal_Details .Dateleft > dateadd(y,9-20-2011,-1)))
or DEATHBENEFIT Dbdate > dateadd(y,9-20-2011,-1)
or PENSIONER_DETAIL.Pdpencomm > dateadd(y,9-20-2011,-1)
or TRANSFER_OUT_HISTORY > dateadd(y,9-20-2011,-1)
you can just combine CURRStatus in one instead of an "OR"
Select * From Personal_Details
Where
((Personal_Details.CURRSta
and (Personal_Details .Dateleft > dateadd(y,9-20-2011,-1)))
or DEATHBENEFIT Dbdate > dateadd(y,9-20-2011,-1)
or PENSIONER_DETAIL.Pdpencomm
or TRANSFER_OUT_HISTORY > dateadd(y,9-20-2011,-1)
ASKER
Hi,
THanks for the reply.
But here I am referring to 4 tables
Select * From Personal_Details 'table Personal_Details
Where
((Personal_Details.CURRSta tus in ('Active','PreservedPensio ner','Pens ioner','Wi dow','Chil d','No Liability'))
and (Personal_Details .Dateleft > dateadd(y,9-20-2011,-1)))
or DEATHBENEFIT Dbdate > dateadd(y,9-20-2011,-1) 'table DEATHBENEFIT
or PENSIONER_DETAIL.Pdpencomm > dateadd(y,9-20-2011,-1)' table PENSIONER_DETAIL
or TRANSFER_OUT_HISTORY > dateadd(y,9-20-2011,-1) 'table TRANSFER_OUT_HISTORY
Cheers
THanks for the reply.
But here I am referring to 4 tables
Select * From Personal_Details 'table Personal_Details
Where
((Personal_Details.CURRSta
and (Personal_Details .Dateleft > dateadd(y,9-20-2011,-1)))
or DEATHBENEFIT Dbdate > dateadd(y,9-20-2011,-1) 'table DEATHBENEFIT
or PENSIONER_DETAIL.Pdpencomm
or TRANSFER_OUT_HISTORY > dateadd(y,9-20-2011,-1) 'table TRANSFER_OUT_HISTORY
Cheers
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cheers mates!!
You can include 'NO Liability' also in the IN clause because you are checking with OR condition with the same column.
This is simple brackets magic.
What are your requirements. If we know that refactoring the query will be easy