Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Complex Query with JOINS & SUBQUERIES

Author  Topic 

mccabr
Starting Member

2 Posts
Posted - 2009-09-08 : 17:01:08
I'm just going to post what I have which is a script that doesn't work as I expect.

SELECT    ET.ey_id, EO.eo_id, OP.op_id, DI.di_id, (OP.op_FirstName + ' ' + OP.op_LastName + '+' + DI.di_FirstName + ' ' + DI.di_LastName) AS TestTeam,

		 (SELECT    SUM(pa_VoucherCode.vc_Value) AS DispensingVoucher
		  FROM      pa_DispensingClaim AS DC INNER JOIN
						   pa_VoucherCode ON DC.dc_vcid = pa_VoucherCode.vc_id
		  WHERE   (DC.disabled = 0) AND (DC.dc_eoid = EO.eo_id)) AS DispenseVoucherAmount,

		 (SELECT    SUM(pa_TintClaimCodes.tc_Value) AS DispensingTint
		  FROM      pa_DispensingClaim AS DC INNER JOIN
						   pa_TintClaimCodes ON DC.dc_tcid = pa_TintClaimCodes.tc_id
		  WHERE   (DC.disabled = 0) AND (DC.dc_eoid = EO.eo_id)) AS TintAmount,

		 (SELECT    SUM(pa_PrismClaimCodes.pl_Value) AS DispensingPrism
		  FROM      pa_DispensingClaim AS DC INNER JOIN
						   pa_PrismClaimCodes ON DC.dc_plid = pa_PrismClaimCodes.pl_id
		  WHERE   (DC.disabled = 0) AND (DC.dc_eoid = EO.eo_id)) AS PrismAmount,

		(SELECT SUM(cc_value) AS STClaim
		 FROM		pa_STClaimCode CC
		 WHERE		(cc.cc_id = SC.sc_ccid)) AS STClaimAmount

FROM     pa_EyeTest AS ET INNER JOIN
                 pa_EyeOrder AS EO ON ET.ey_id = EO.eo_eyid INNER JOIN
                 pa_Optician AS OP ON ET.ey_optician = OP.op_id INNER JOIN
                 pa_Dispenser AS DI ON ET.ey_dispenser = DI.di_id INNER JOIN
				 pa_STClaim as SC ON ET.ey_id = SC.sc_eyid
WHERE   (EO.disabled = 0)


The Query should output one record per entry in PA_EYETEST(ET), PA_EYEORDER(EO) will have multiple records for each parent record, and I am subquerying linked tables to this for aggregates. PA_STCLAIM also has multiple records per parent record.

I have more joins to add to this but I am struggling to get something like the following:

EY_ID EO_ID SUM(DispenseVoucherAmount) .... SUM(STClaimAmount)

There are multiple records as there may be two child ORDERS and three STCLAIMS, I think the problem is that I am trying to join queries of different shapes but I'm hoping someone here can make sense of where I'm going wrong.

I have been working on this for 5 days trying a whole manner of different approaches and am currently in despair...

Any help, even to say I can't do this would be welcome.

fordc03
Starting Member

3 Posts
Posted - 2009-09-08 : 17:34:47
Is it possible to post a sample of the output instead of describing it?

What you've posted doesn't really give a full picture into what you're trying to do.
Go to Top of Page

mccabr
Starting Member

2 Posts
Posted - 2009-09-08 : 17:43:06
Certainly!

ey_id       eo_id       op_id       di_id       TestTeam                                                                                                                    DispenseVoucherAmount TintAmount            PrismAmount           STClaimAmount
----------- ----------- ----------- ----------- --------------------------------------------------------------------------------------------------------------------------- --------------------- --------------------- --------------------- ---------------------------------------
7           15          39          2           Dispense Only+Dispense Only                                                                                                 71.00                 0.00                  0.00                  74.02
32          20          42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
44          23          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  74.02
45          25          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  74.02
50          26          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
50          27          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
55          28          42          5           Test Optician+Test Dispenser                                                                                                78.10                 0.00                  0.00                  48.52
59          29          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
59          31          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  48.52
60          33          39          2           Dispense Only+Dispense Only                                                                                                 54.00                 0.00                  0.00                  48.52
63          34          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  48.52
63          35          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  48.52
72          38          42          5           Test Optician+Test Dispenser                                                                                                178.40                0.00                  0.00                  48.52
35          39          42          5           Test Optician+Test Dispenser                                                                                                150.90                0.00                  0.00                  74.02
35          39          42          5           Test Optician+Test Dispenser                                                                                                150.90                0.00                  0.00                  61.00
35          39          42          5           Test Optician+Test Dispenser                                                                                                150.90                0.00                  0.00                  74.02
41          40          42          5           Test Optician+Test Dispenser                                                                                                108.00                7.80                  0.00                  74.02
41          41          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  74.02
40          42          42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
38          43          42          5           Test Optician+Test Dispenser                                                                                                178.40                0.00                  0.00                  48.52
38          44          42          5           Test Optician+Test Dispenser                                                                                                178.40                0.00                  0.00                  48.52
36          45          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  48.52
36          46          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  48.52
42          47          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  48.52
37          48          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
37          48          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  40.00
37          49          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
37          49          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  40.00
43          50          42          5           Test Optician+Test Dispenser                                                                                                108.00                0.00                  0.00                  48.52
43          51          42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
33          52          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  23.20                 48.52
33          53          42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
75          55          42          5           Test Optician+Test Dispenser                                                                                                61.40                 0.00                  0.00                  48.52
75          55          42          5           Test Optician+Test Dispenser                                                                                                61.40                 0.00                  0.00                  40.00
76          56          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
76          57          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
84          59          42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
84          59          42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
91          60          42          5           Test Optician+Test Dispenser                                                                                                61.40                 0.00                  0.00                  48.52
86          61          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
95          62          42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
87          63          42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
87          63          42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
87          64          42          5           Test Optician+Test Dispenser                                                                                                78.10                 0.00                  0.00                  48.52
87          64          42          5           Test Optician+Test Dispenser                                                                                                78.10                 0.00                  0.00                  48.52
85          67          42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
85          68          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  48.52
79          69          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
83          70          42          5           Test Optician+Test Dispenser                                                                                                78.10                 0.00                  0.00                  48.52
92          71          42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
96          72          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  48.52
99          73          42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
99          74          42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
94          75          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
93          76          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  48.52
93          77          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  48.52
100         78          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
103         79          42          5           Test Optician+Test Dispenser                                                                                                61.40                 0.00                  0.00                  48.52
102         80          42          5           Test Optician+Test Dispenser                                                                                                78.10                 0.00                  0.00                  48.52
104         81          42          5           Test Optician+Test Dispenser                                                                                                61.40                 0.00                  0.00                  48.52
106         82          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
105         83          42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
106         84          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  48.52
105         85          42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
107         86          42          5           Test Optician+Test Dispenser                                                                                                61.40                 0.00                  0.00                  48.52
108         87          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
109         88          42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
108         89          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
111         90          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
111         91          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  48.52
112         92          42          5           Test Optician+Test Dispenser                                                                                                178.40                0.00                  0.00                  29.52
112         93          42          5           Test Optician+Test Dispenser                                                                                                178.40                0.00                  0.00                  29.52
113         94          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  74.02
113         95          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  74.02
114         96          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  74.02
114         97          42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  74.02
115         98          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  48.52
115         99          42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  23.20                 48.52
60          100         39          2           Dispense Only+Dispense Only                                                                                                 54.00                 0.00                  0.00                  48.52
62          101         42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
62          102         42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
72          103         42          5           Test Optician+Test Dispenser                                                                                                178.40                0.00                  0.00                  48.52
117         104         42          5           Test Optician+Test Dispenser                                                                                                61.40                 0.00                  0.00                  48.52
118         105         42          5           Test Optician+Test Dispenser                                                                                                61.40                 0.00                  0.00                  48.52
126         108         42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  74.02
126         109         42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  74.02
132         117         42          5           Test Optician+Test Dispenser                                                                                                178.40                8.80                  0.00                  48.52
35          118         42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  74.02
35          118         42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  61.00
35          118         42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  74.02
35          119         42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  74.02
35          119         42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  61.00
35          119         42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  74.02
40          120         42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
40          121         42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
146         1043        42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  74.02
148         1047        39          2           Dispense Only+Dispense Only                                                                                                 35.50                 0.00                  0.00                  74.02
150         1049        44          6           Yorkshire Optician+Yorkshire Branch                                                                                         54.00                 0.00                  0.00                  12.00
150         1050        44          6           Yorkshire Optician+Yorkshire Branch                                                                                         54.00                 0.00                  0.00                  12.00
151         1051        49          6           Dispense Only+Yorkshire Branch                                                                                              35.50                 0.00                  0.00                  12.00
151         1052        49          6           Dispense Only+Yorkshire Branch                                                                                              35.50                 0.00                  0.00                  12.00
155         1055        44          6           Yorkshire Optician+Yorkshire Branch                                                                                         61.40                 0.00                  0.00                  12.00
157         1061        42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  74.02
158         1062        42          5           Test Optician+Test Dispenser                                                                                                NULL                  NULL                  NULL                  48.52
159         1063        42          5           Test Optician+Test Dispenser                                                                                                61.40                 0.00                  0.00                  48.52
160         1064        42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
160         1065        42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
161         1066        42          5           Test Optician+Test Dispenser                                                                                                54.00                 0.00                  0.00                  48.52
172         1123        42          5           Test Optician+Test Dispenser                                                                                                35.50                 0.00                  0.00                  74.02
173         1156        39          2           Dispense Only+Dispense Only                                                                                                 182.70                0.00                  0.00                  61.00
174         1157        39          5           Dispense Only+Test Dispenser                                                                                                35.50                 0.00                  0.00                  48.52
174         1157        39          5           Dispense Only+Test Dispenser                                                                                                35.50                 0.00                  0.00                  61.00
Don't know if this is clear but eo_id 39 has 3 records with a DispenseVoucherAmount of 150.90 when this should be a single record of that value and the last column I would like to see the 3 rows of 74.02, 61.00, 74.02 SUM'd as 209.04?
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site