clause added the condition....
SELECT
R.TEAMING_AGREEMENT_ID,
R.ROUTE_GROUP_ID,
B.BUSINESS_NAME,
T.OPPORTUNITY_ID,
D.DOCUMENT_NAME AS TEAMING_AGREEMENT_DOCUMENT,
D.DOCUMENT_EXTENSION AS TEAMING_AGREEMENT_DOCUMENT_EXTENSION ,
D.DOCUMENT_TYPE_ID AS TEAMING_AGREEMENT_DOCUMENT_ID,
D2.DOCUMENT_NAME AS ATTACHMENT_A_DOCUMENT,
D2.DOCUMENT_EXTENSION AS ATTACHMENT_A_DOCUMENT_EXTENSION,
D2.DOCUMENT_TYPE_ID AS ATTACHMENT_A_DOCUMENT_ID,
FROM
TEAMING_AGREEMENT_ROUTING R
INNER JOIN TEAMING_AGREEMENT T
ON (R.TEAMING_AGREEMENT_ID = T.TEAMING_AGREEMENT_ID)
INNER JOIN COMMUNITY_BUSINESS B
ON (T.COMMUNITY_BUSINESS_ID = B.COMMUNITY_BUSINESS_ID)
INNER JOIN TEAMING_AGREEMENT_TYPE TAT
ON (T.TEAMING_AGREEMENT_TYPE_ID =
TAT.TEAMING_AGREEMENT_TYPE_ID)
INNER JOIN TEAMING_AGREEMENT_DOCUMENT D
ON (T.TEAMING_AGREEMENT_ID = D.TEAMING_AGREEMENT_ID AND
D.DOCUMENT_TYPE_ID = 1)
INNER JOIN TEAMING_AGREEMENT_DOCUMENT D2
ON (T.TEAMING_AGREEMENT_ID = D2.TEAMING_AGREEMENT_ID AND
D2.DOCUMENT_TYPE_ID = 2)
WHERE
T.OPPORTUNITY_ID = 33
GROUP BY
B.BUSINESS_NAME,
R.TEAMING_AGREEMENT_ID,
R.ROUTE_GROUP_ID,
T.OPPORTUNITY_ID,
D.DOCUMENT_NAME,
D.DOCUMENT_EXTENSION,
D.DOCUMENT_TYPE_ID,
D2.DOCUMENT_NAME,
D2.DOCUMENT_EXTENSION,
D2.DOCUMENT_TYPE_ID
Mike
> Given the following:
>
> SELECT
> R.TEAMING_AGREEMENT_ID,
> R.ROUTE_GROUP_ID,
> B.BUSINESS_NAME,
> T.OPPORTUNITY_ID,
> D.DOCUMENT_NAME,
> D.DOCUMENT_EXTENSION
> FROM
> TEAMING_AGREEMENT_ROUTING R
> INNER JOIN TEAMING_AGREEMENT T
> ON (R.TEAMING_AGREEMENT_ID = T.TEAMING_AGREEMENT_ID) INNER
> JOIN COMMUNITY_BUSINESS B
> ON (T.COMMUNITY_BUSINESS_ID = B.COMMUNITY_BUSINESS_ID)
> INNER JOIN TEAMING_AGREEMENT_TYPE TAT
> ON (T.TEAMING_AGREEMENT_TYPE_ID =
> TAT.TEAMING_AGREEMENT_TYPE_ID) INNER JOIN
> TEAMING_AGREEMENT_DOCUMENT D
> ON (T.TEAMING_AGREEMENT_ID = D.TEAMING_AGREEMENT_ID) WHERE
> T.OPPORTUNITY_ID = 33 GROUP BY B.BUSINESS_NAME,
> R.TEAMING_AGREEMENT_ID, R.ROUTE_GROUP_ID, T.OPPORTUNITY_ID,
> D.DOCUMENT_NAME, D.DOCUMENT_EXTENSION
>
> If I need to get two of documents from the
> TEAMING_AGREEMENT_DOCUMENT table (different types: DOC_TYPE =
> 1 and DOC_TYPE =2), how can I do this and get the desired
> number of records returned. If I remove the joined in
> TEAMING_AGREEMENT_DOCUMENT table I return 5 records, which is
> correct, but when I try and get the two documents associated
> I return 10 records. Is there anyway to qualify the selection
> of the documents? I hope this makes sense.... grouping? sub query?
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
