Query over multiple tables

[eussias]

I need to run a query which will return the majority of fields from 4 tables. All have a common field, ClientNum. The problem is that there can be more than instance of ClientNum in two of the tables. What would be the easiest way to extract all this data??

 

[rac2]

If you can live with the possibility of multiple rows for one ClientNum then simply join the tables on ClientNum and SQL will take care of the rest -

SELECT t1.Name, t1.Age, t2.VisitDate, t3.Diagnosis, t4.PaymentSchedule
FROM table_one t1
JOIN table_two t2 on t1.ClientNum = t2.ClientNum
JOIN table_three t3 on t1.ClientNum = t3.ClientNum
JOIN table_four t4 on t1.ClientNum = t4.ClientNum

If you must have a single row for each ClientNum and you know the maximum number of occurences of the multiple rows in the tables and you must see the data in all of them it can be done if there is column like a counter in the tables with multiple rows but it is complex.

SELECT t1.Name, t1.Age, t2.VisitDate, t5.VisitDate, t3.Diagnosis, t4.PaymentSchedule
FROM table_one t1
JOIN table_two t2 on t1.ClientNum = t2.ClientNum
JOIN table_three t3 on t1.ClientNum = t3.ClientNum
JOIN table_four t4 on t1.ClientNum = t4.ClientNum
JOIN table_two t5 on t1.ClientNum = t5.ClientNum
WHERE t2.occurence = 1
  AND t5.occurence = 2

If you must have a single row for each ClientNum and you don't know the maximum number of occurences then you are out of luck.