sql query with 4 tables

Hi,

Here I am referring to 4 tables any help with joins is appreciated.


Select *  From Personal_Details 'table Personal_Details
Where  
((Personal_Details.CURRStatus in ('Active','PreservedPensioner','Pensioner','Widow','Child','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
RIASAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sdstuberConnect With a Mentor Commented:
do you have any columns between the tables that should be linked?

for example....

if you have 100 rows in personal_details that meet your status and date criteria

and if you have 100 rows in transfer_out_history that meet your date criteria

then your results will have 10000 rows because nothing above has information restricting the relationships between the two tables.

So,  every row in each table will be joined to every row in the other table.

Same with the other two tables.  


If each table had a mere 100 rows matching their respective criteria above,
  that would yield 100000000  rows total in the output

0
 
sdstuberCommented:
something like this?

SELECT *
  FROM personal_details
      JOIN deathbenefit
          ON ((personal_details.currstatus IN
                   ('Active', 'PreservedPensioner', 'Pensioner', 'Widow', 'Child', 'No Liability'))
              AND (personal_details.dateleft > dateadd(y, 9 - 20 - 2011, -1)))
             OR deathbenefit.dbdate > dateadd(y, 9 - 20 - 2011, -1)
      JOIN pensioner_detail
          ON pensioner_detail.pdpencomm > dateadd(y, 9 - 20 - 2011, -1)
      JOIN transfer_out_history
          ON transfer_out_history > dateadd(y, 9 - 20 - 2011, -1)
0
 
RIASAuthor Commented:
Thanks for the reply but the query loops infinitely.

Cheers
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
RIASAuthor Commented:
Hi,
This is a spec, all marked in bold are tables:

Where  [Personal_details.currStatus] at '21-Sept-2011'  = “Active” or “Preserved Pensioner” or “Pensioner” or  “Widow(er)”  or “Child”
OR where  [Personal_details.Status] at '21-Sept-2011'   = “No Liability”
and  [Personal_details.Dateleft], [death_benefit..Dbdate], [pensioner_detail.Pdpencomm] or [Transfer_out_history.Topaid]  later than (%Input_Date% less 1 Year)

Cheers
0
 
sdstuberCommented:
>>> query loops infinitely.

how does that query "loop"  ?

it might take a long time, but it can't loop

can you give some sample data and expected results?
0
 
RIASAuthor Commented:
Hi,
The query resulted  in 213000 rows.There was only one number duplicated 213000 times and also it needed to be cancelled as it was executing even after 213000 .

Cheers
0
 
sdstuberCommented:
perhaps your join conditions aren't restrictive enough
 so you're seeing the cartesian explosion of extra rows
0
 
RIASAuthor Commented:
Cheers mate
0
All Courses

From novice to tech pro — start learning today.