sql query with 4 tables

Posted on 2011-09-21
Last Modified: 2012-05-12

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

Select *  From Personal_Details 'table Personal_Details
((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

Question by:RIAS
  • 4
  • 4
LVL 73

Expert Comment

ID: 36574223
something like this?

  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)

Author Comment

ID: 36574369
Thanks for the reply but the query loops infinitely.


Author Comment

ID: 36574397
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)

LVL 73

Expert Comment

ID: 36574438
>>> 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?
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.


Author Comment

ID: 36574526
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 .

LVL 73

Expert Comment

ID: 36574756
perhaps your join conditions aren't restrictive enough
 so you're seeing the cartesian explosion of extra rows
LVL 73

Accepted Solution

sdstuber earned 500 total points
ID: 36574858
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


Author Closing Comment

ID: 36813697
Cheers mate

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL: Subtracting Amounts from "Among Rows" 3 41
SQL Select Statement 2 20
recover sqlserver db 8 51
Oracle 10g - Select rows into colums 4 5
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now