With help from this list I ended up creating a Stored Procedure, placing
the information in multiple temp tables then joining the temp tables.
Something like:
SELECT
OPPORTUNITY_ID AS C1,
TEXT_TYPE_ID AS C2,
OPPORTUNITY_TEXT_DATA AS C3
into Temp1
FROM
OPPORTUNITY_TEXT
WHERE
(TEXT_TYPE_ID = 12 )
SELECT
OPPORTUNITY_ID AS C1,
TEXT_TYPE_ID AS C2,
OPPORTUNITY_TEXT_DATA AS C3
into Temp2
FROM
OPPORTUNITY_TEXT
WHERE
(TEXT_TYPE_ID = 13 )
SELECT
TEMP1.C1,
TEMP1.C2,
TEMP1.C3
TEMP2.C3 AS C4
FROM
temp1 INNER JOIN temp2 on temp1.OPPORTUNITY_ID = temp2.OPPORTUNITY_ID
WHERE
OPPORTUNITY_ID = 1
Hope this helps
Rodney
-----Original Message-----
From: Tangorre, Michael [mailto:[EMAIL PROTECTED]
Sent: Monday, March 15, 2004 12:10 PM
To: CF-Talk
Subject: SOT: SQL QUESTION
SQL SERVER 2000
CFMX 6.1
I have three tables called: OPPORTUNITY, OPPORTUNITY_TEXT, TEXT_TYPE
OPPORTUNITY_TEXT has two columns making the PK: TEXT_TYPE_ID and
OPPORTUNITY_ID. In addition there is a column called OPPORTUNITY_TEXT_DATA
which holds the actual text I am after [varchar(6000)]. What I am trying to
do is pull back two specific records if they exist but as one record. Let me
elaborate....
Currently, I can say the following:
SELECT
OPPORTUNITY_ID AS C1,
TEXT_TYPE_ID AS C2,
OPPORTUNITY_TEXT_DATA AS C3
FROM
OPPORTUNITY_TEXT
WHERE
OPPORTUNITY_ID = 1
AND
(TEXT_TYPE_ID = 12 OR TEXT_TYPE_ID = 13)
Which returns two records as expected.
-----------------------------
C1 | C2 | C3
------|-----------|----------
1 | 12 | AAAAA
-----------------------------
1 | 13 | BBBBB
-----------------------------
What I would like to do is join on itself and alias the columns so I can get
the output to be something like so that I can avoid all the conditional
logic in my page that would be needed to output the correct value in the
correct form field:
------------------------
Alias1 | Alias2
------------|-----------
AAAAA | BBBBB
------------------------
Does that make sense? Am I missing the boat or something here. Open for
suggestions and assistance.
Thanks!
Mike
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
