Correlated SUB Queries?

I would like to combine the following 3 select statements:

1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')

2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'

3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'

the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).

the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).

The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)

Thanks a lot,

Dave Benoiti'm not sure where the correlation comes in

these are uncorrelated subqueries --select SECTION_ENGLISH_DESC
, D_REGULATION.REG_ENGLISH_DESC
, D_SECTION.REG_SURR_ID
, ( select count(*)
from F_INSPECTIONS
where REG_SURR_ID = '101' ) as inspections
, case when
( select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID = '201' ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')if these really should be correlated, then you can add a WHERE condition to each subquery that references one of the outer tables (D_SECTION or D_REGULATION)|||to clarify
these are not correlated subqueries because the inner query does not reference the outer query with an table alias.|||however, thats exactly what i need to do. In this section:

select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID = '201' ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies

I actually need to selelct the count(*) of inspections where section_surr_id = the current section_surr_id in the top sql statemtent. (I just hard coded 201 but it should be the current section_id.|||I actually need to selelct the count(*) of inspections where section_surr_id = the current section_surr_id in the top sql statemtent. (I just hard coded 201 but it should be the current section_id.yeah, that was the missing info, wasn't itselect SECTION_ENGLISH_DESC
, D_REGULATION.REG_ENGLISH_DESC
, D_SECTION.REG_SURR_ID
, ( select count(*)
from F_INSPECTIONS
where REG_SURR_ID
= D_SECTION.REG_SURR_ID ) as inspections
, case when
( select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID
= D_SECTION.REG_SURR_ID ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')if that's not the right correlation, at least now you know how to do it

;) :)|||Hi, Thanks for the answer but im still a bit confused on that same section.

The thing is that I have to find the current inspection number to see if there were any VIOLATIONS (if there was 1 or more VIOLATIONS!!, then its not compliant, otherwise it is considered compliant).

NOTICE the alais currSecID I created in order to make a link between the current SECTION_ID (not regulation_ID) and the one used in the VIOLATIONS Compliant section (above)

select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID,
D_SECTION.SECTION_SURR_ID currSecID,
( select count(*)
from F_INSPECTIONS
where REG_SURR_ID
= D_SECTION.REG_SURR_ID ) as inspections,
case when
( select count(*)
from F_VIOLATIONS
where SECTION_SURR_ID = currSecID) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')|||don't use the alias in the subquery

Correlated SUB Queries?

I would like to combine the following 3 select statements:

1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')

2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'

3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'

the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).

the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).

The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)

Thanks a lot,

Dave Benoitplease don't cross-post

http://www.dbforums.com/showthread.php?t=1117027sql

Correalated sub...

i have a historical tale with seven different columns in it. I would like to join to this table and get the latest data for a particular key in it, the problem is one row in the historical table may only have one updated column in which case i need a compilatoun of several different rows to make the 'latest' row.(i.e. one updated row may only update one field...) i think i know what i want to do code follows... but it doesnt work... any ideas?
SELECT
CTRP.CUSIP as CUSIP,
CTRP.PORTFOLIO_NAME as PORTFOLIO,
CF.FACTOR * TRCURORIGFACE as CUR_FACE,
CF.FACTOR as CUR_FACTOR,
CPA.PX_ASK as CUR_PX_ASK,
CPS.PX_SPD AS CUR_PX_SPD,
CPSD.PX_SPD_DT AS CUR_PX_SPD_DT,
DMB.DISC_MRGN_BID AS DISC_MRGN_BID,
CWC.WAL_CALL AS CUR_WAL_CALL,
CT.TRMODDUR AS CUR_TRMODDUR,
CPA.RECORD_DATE AS CPA_RT,
CPS.RECORD_DATE AS CPS_RT,
CPSD.RECORD_DATE AS CPSD_RT,
DMB.RECORD_DATE AS DMB_RT,
CMC.RECORD_DATE AS CMC_RT,
CWC.RECORD_DATE AS CWC_RT,
CT.RECORD_DATE AS CT_RT,
CF.RECORD_DATE AS CF_RT

FROM
(SELECT
DEAL_BOND_NAME AS CUSIP,
DEAL_BOND_ID,
sum(trcurorigface * case tran_deal_type_name when 'Buy' then 1 when 'Sell' then -1 end) AS trcurorigface,
PORTFOLIO_NAME
FROM
VIEW_TRAN_DEAL_BOND
LEFT OUTER JOIN DEAL_BOND DB ON DB.ID = VIEW_TRAN_DEAL_BOND.DEAL_BOND_ID
WHERE
hedge = 0 OR hedge is null
GROUP BY
PORTFOLIO_NAME,
DEAL_BOND_NAME,
DEAL_BOND_ID) CTRP
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CF ON CF.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CPA ON CPA.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CPS ON CPS.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CPSD ON CPSD.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY DMB ON DMB.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CMC ON CMC.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CWC ON CWC.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CT ON CT.ID = CTRP.DEAL_BOND_ID
WHERE
CPA.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH1
WHERE PX_ASK IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH1.DEAL_BOND_ID) and
CF.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH2
WHERE FACTOR IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH2.DEAL_BOND_ID) and
CPS.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH3
WHERE PX_SPD IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH3.DEAL_BOND_ID) and
CPSD.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH4
WHERE PX_SPD_DT IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH4.DEAL_BOND_ID) and
DMB.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH5
WHERE DISC_MRGN_BID IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH5.DEAL_BOND_ID) and
CWC.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH6
WHERE WAL_CALL IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH6.DEAL_BOND_ID) and
CT.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH7
WHERE TRMODDUR IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH7.DEAL_BOND_ID)

You should be able to create sub-queries that select the latest data for each of the seven fields by the key and join those seven via the key using a where clause in each of the sub-queries to filter out where your respective field is not NULL. In the sub-queries, be sure to order by the record date and only select top 1. An extremely simplified example follows:

select
t.Key,
t1.Field1,
t2.Field2
from
Table t
inner join
(
select top 1
t.Field1
from
Table t
where
t.Key = <Parameter Value>
and Field1 is not null
order by
t.RecordDate
) t1
on
t1.Key = t.Key
inner join
(
select top 1
t.Field2
from
Table t
where
t.Key = <Parameter Value>
and t.Field2 is not null
order by
t.RecordDate
) t2
on t2.Key = t.Key
where
t.Key = <Parameter Value>

I don't think your example is working because you're asking for a certain row where the Record_Date field is equal to (possibly) seven different values at the same time, which is impossible.

Corelated Sub query

Hi,

I'm using this query, It works fine, except that i want to show PayType. When i tried to show it, I'll get duplicate records

What can i do?

Code Snippet

SELECT tbl_TransPayLnk.TransPayID, tbl_TransPayLnk.TransDate, tbl_TransPayLnk.Operator, tbl_TransPayLnk.Flagged, tbl_TransPayLnk.Remarks,

tbl_TransPayLnk.RemarksDate, tbl_Transaction.RefNo, tbl_Transaction.TransAmount, tbl_TransPayLnk.TransNo AS Expr1, tbl_TransPayLnk.PayID,

tbl_FundCode.FundDescription

FROM tbl_TransPayLnk LEFTOUTERJOIN

tbl_FundCode INNERJOIN

tbl_Transaction ON tbl_FundCode.FundCodeID = tbl_Transaction.FundCodeID ON tbl_TransPayLnk.TransNo = tbl_Transaction.TransNo

WHEREEXISTS

(SELECT PaymentID, PayID, PayType, Amount

FROM tbl_Payment AS tbl_Payment_1

WHERE(PayID = tbl_TransPayLnk.PayID))

Thanks

Prince:

I assume by your post that to add "PayType" that you need to also join to the tbl_Payment table. I would normally expect that the amounts would vary from record to record in that table. Also, I question the use of showing the "payType" without also showing the amount. If all you truely want is the "PayType" then changing your "SELECT" to a "SELECT DISTINCT" might be in order. Also, experiment with adding the AMOUNT and possibly the "PaymentID" to the data returned and see if the data makes more "sense" that way.

Kent

|||

Prince

Try this:

Code Snippet

SELECT tbl_TransPayLnk.TransPayID, tbl_TransPayLnk.TransDate, tbl_TransPayLnk.Operator,

tbl_TransPayLnk.Flagged, tbl_TransPayLnk.Remarks,

tbl_TransPayLnk.RemarksDate, tbl_Transaction.RefNo, tbl_Transaction.TransAmount,

tbl_TransPayLnk.TransNo AS Expr1, tbl_TransPayLnk.PayID,

tbl_FundCode.FundDescription,

tbl_Payment_1.PayType

FROM tbl_TransPayLnk

LEFTOUTERJOIN tbl_FundCode

INNERJOIN tbl_Transaction

ON tbl_TransPayLnk.TransNo = tbl_Transaction.TransNo

INNERJOIN

(

SELECTDISTINCT PayID, PayType

FROM tbl_Payment

)AS tbl_Payment_1

ON tbl_TransPayLnk.PayID = tbl_Payment_1.PayID

P.S. Some sample DDL and sample data would sure make it easier to assist you....

|||

Hi,

Right i tried your solution and got an error :

Code Snippet

Msg 102, Level 15, State 1, Line 31

Incorrect syntax near 'PayID'.

As requested here are table structure and data:

Tbl_Transaction

Code Snippet

TransactionID int Unchecked
TransNo varchar(50) Checked
RefNo nvarchar(50) Checked
FundCodeID smallint Checked
TransAmount decimal(18, 2) Checked
Description varchar(100) Checked

Data in Tbl_Transaction

Code Snippet

TransactionID TransNo RefNo FundCodeID TransAmount Description 13 1001 20013808 1 28.55 14 1001 34983249 2 9.31

Tbl_TransPayLnk

Code Snippet

TransPayID int Unchecked
PayID int Checked
TransNo varchar(50) Checked
TransDate datetime Checked
Operator char(5) Checked
TerminalID tinyint Checked
Flagged char(1) Unchecked
Remarks text Checked
RemarksDate datetime Checked
TPayment decimal(18, 2) Checked

Data Tbl_TransPayLnk

Code Snippet

TransPayID PayID TransNo TransDate Operator TerminalID Flagged Remarks RemarksDate TPayment 5 1 1001 09/05/2007 02 2 V hi there 13/06/2007 37.86 6 1003 1002 09/05/2007 02 2 N 73.33

Tbl_Payment

Code Snippet

PaymentID int Unchecked
PayID int Checked
PayType varchar(50) Checked
Amount decimal(18, 2) Checked

Data Tbl_Payment

Code Snippet

PaymentID PayID PayType Amount 17 1 Cheque 30 18 1 Cash 7.86 19 1003 Cheque 73.33

And this is what i would need in one ROW

TransPayID TransDate Operator Flagged Remarks RemarksDate RefNo TransAmount TransNo PayID FundDescription 5 09/05/2007 02 V hi there 13/06/2007 20013808 28.55 1001 1 Rent 5 09/05/2007 02 V hi there 13/06/2007 34983249 9.31 1001 1 Sundry Debtor 6 09/05/2007 02 N 20013808 73.33 1002 1003 TV Licence

the only thing i want more in there is PAY TYPE without duplicating the rows. I mean as it is.

If i would add Payment tbl regardless of the joins i would be duplicating ROW 5 2 more times. so .......

Which would be cheque or cash.

Any ideas.

Thanks

|||

Which row out of tbl_Payment do you want to be in the result?

How do you determine that that row should be it?

Do you just want one, you don't care which?

Or the highest amount?

|||

Hi,

Well

TransPayID TransDate Operator Flagged Remarks RemarksDate RefNo TransAmount TransNo PayID FundDescription 5 09/05/2007 02 V hi there 13/06/2007 20013808 28.55 1001 1 Rent 5 09/05/2007 02 V hi there 13/06/2007 34983249 9.31 1001 1 Sundry Debtor 6 09/05/2007 02 N 20013808 73.33 1002 1003 TV Licence

If you look at this i'm getting PayID which is one. Against one in tbl_payment there is PayType. I just want PayType so cheque or cash would be against record PayID = 1

Thanks

|||

Prince:

What is the complete key to the tbl_payment table? Also, doesn't the fact that the LEFT JOIN table participate in a subsequent inner join:

Code Snippet

INNERJOIN

tbl_Transaction ON tbl_FundCode.FundCodeID = tbl_Transaction.FundCodeID ON tbl_TransPayLnk.TransNo = tbl_Transaction.TransNo

turn the LEFT JOIN into a defacto INNER JOIN?

|||

Hello Kent,

The primary key for tbl_Payment is PaymentID but that is not how i join. I joined it to tbl_transPayLnk using PayID which is copied in both these tables.

I don't know but what else can i do?

Read my long post and you get the idea of what i want?

Thanks

|||

Prince

I commented out the reference to the FundCode table since you didn't provide the data.

Just uncomment it and add your ON clause to include that data.

Code Snippet

createtable tbl_Transaction (

TransactionID int,

TransNo varchar(50),

RefNo nvarchar(50),

FundCodeID smallint,

TransAmount decimal(18, 2),

Description varchar(100)

)

createtable tbl_TransPayLnk (

TransPayID int,

PayID int,

TransNo varchar(50),

TransDate datetime,

Operator char(5),

TerminalID tinyint,

Flagged char(1),

Remarks text,

RemarksDate datetime,

TPayment decimal(18, 2)

)

createtable tbl_Payment (

PaymentID int,

PayID int,

PayType varchar(50),

Amount decimal(18, 2)

)

setdateformat dmy

insertinto tbl_Transaction values(13,'1001','20013808', 1, 28.55,null)

insertinto tbl_Transaction values(14,'1001','34983249', 2, 9.31,null)

insertinto tbl_TransPayLnk values(5, 1,'1001','09/05/2007','02', 2,'V','hi there','13/06/2007', 37.86)

insertinto tbl_TransPayLnk values(6, 1003,'1002','09/05/2007','02', 2,'N',null,null, 73.33)

insertinto tbl_Payment values(17, 1,'Cheque', 30)

insertinto tbl_Payment values(18, 1,'Cash', 7.86)

insertinto tbl_Payment values(19, 1003,'Cheque', 73.33)

SELECT tbl_TransPayLnk.TransPayID, tbl_TransPayLnk.TransDate, tbl_TransPayLnk.Operator,

tbl_TransPayLnk.Flagged, tbl_TransPayLnk.Remarks,

tbl_TransPayLnk.RemarksDate, tbl_Transaction.RefNo, tbl_Transaction.TransAmount,

tbl_TransPayLnk.TransNo AS Expr1, tbl_TransPayLnk.PayID,

--tbl_FundCode.FundDescription,

tbl_Payment_1.PayType

FROM tbl_TransPayLnk

--LEFT OUTER JOIN tbl_FundCode

INNERJOIN tbl_Transaction

ON tbl_TransPayLnk.TransNo = tbl_Transaction.TransNo

INNERJOIN

(

SELECTDISTINCT PayID, PayType

FROM tbl_Payment

)AS tbl_Payment_1

ON tbl_TransPayLnk.PayID = tbl_Payment_1.PayID

and tbl_Payment_1.PayType =

(selecttop 1 paytype from tbl_Payment

where tbl_TransPayLnk.PayID = tbl_Payment_1.PayID

)

|||

Hello Dale,

Thank you very much. I checked the code and although it does bring up the records. It only brought back Cheque even for the records that have cash in tbl_payment.

If it works fine, it should have brought cash and cheque for PayID= 1 which it didn't

Any ideas why?

Thanks I really appreciate your help.

|||OK; that helps. Now, are you expecting two lines to be displayed when there are two different pay types or are you wanting something like, 'Cheque, Cash' where the results are strung together? (Please forgive me, Dale)|||

That's where I'm confused on what you're looking for.

How exactly do you want PayType to appear in the result?

|||

There are only 2 pay types so it should appear as it is:

so for PayID =1 both cheque and cash should be displayed but without repeating the record 4 times.

Because rightnow it would do this if you omitt your last query. 2 cash and 2 for cheque for PayID=1

|||

"... Now, are you expecting two lines to be displayed when there are two different pay types or are you wanting something like, 'Cheque, Cash' where the results are strung together? ..."

(Like I said: Please forgive me, Dale... *sigh* )

|||

Code Snippet

createfunction dbo.GetPayType ( @.PayID asint)

returnsvarchar(200)

as

begin

declare @.pt varchar(200)

select @.pt =coalesce( @.pt +',','')+ paytype

from tbl_Payment where payid = @.PayID

return @.pt

end

SELECT tbl_TransPayLnk.TransPayID, tbl_TransPayLnk.TransDate, tbl_TransPayLnk.Operator,

tbl_TransPayLnk.Flagged, tbl_TransPayLnk.Remarks,

tbl_TransPayLnk.RemarksDate, tbl_Transaction.RefNo, tbl_Transaction.TransAmount,

tbl_TransPayLnk.TransNo AS Expr1, tbl_TransPayLnk.PayID,

--tbl_FundCode.FundDescription,

dbo.GetPayType(tbl_TransPayLnk.PayID)as PayType

FROM tbl_TransPayLnk

--LEFT OUTER JOIN tbl_FundCode

INNER JOIN tbl_Transaction

ON tbl_TransPayLnk.TransNo = tbl_Transaction.TransNo