# Thread: 2 Columns as 1?

1. ## 2 Columns as 1?

Hi everyone! I hope all is well on this sad day.

I have a table called relprods that looks like this:
PDID1 int
PDID2 int
times int

I am make a Customers who shopped for this item also shopped for ... feature, and I am having a little trouble.

Say the user is looking at prod number 27:

i want to select pdid1 if pdid2=27 or pdid2 if pdid1=27
but i dont want to have both.....any ideas?

2. ## Re: 2 Columns as 1?

Originally posted by typecero
Hi everyone! I hope all is well on this sad day.

I have a table called relprods that looks like this:
PDID1 int
PDID2 int
times int

I am make a Customers who shopped for this item also shopped for ... feature, and I am having a little trouble.

Say the user is looking at prod number 27:

i want to select pdid1 if pdid2=27 or pdid2 if pdid1=27
but i dont want to have both.....any ideas?
I don't think that you can do that with your SQL, unless you're writing a stored procedure or something. If you're just doing this for a website then let the scripting dictate the logic for that. Here's some ASP code to get you started (If you're a PHP guy I'm sorry, but I am still learning it -- that's why this is in ASP).

[vbs]
'**Assuming a connection is created already
SQL = "SELECT pdid1, pdid2 FROM relprods WHERE pdid1=27 or pdid2=27;"
RS.Open SQL, [connection object], 3, 3, 1
Do Until RS.EOF
'**This will show pdid1 if pdid2=27, or will show
'**pdid2 if pdid1=27
If Trim(cStr(RS.Fields("pdid1"))) = "27" Then
Response.Write(RS.Fields("pdid2"))
ElseIf Trim(cStr(RS.Fields("pdid2"))) = "27" Then
Response.Write(RS.Fields("pdid1"))
Else
Response.Write("Logic Error!<br />")
End If
RS.MoveNext
Loop

[/vbs]

--Vinnie

3. Thanks, However i do have the same thing already in PHP, i am now using ASP.NET however, and i am fine with using stored procedures and/or user defined functions...
I really want to just return a result set from an SQL query, so that i can just bind that to the control i made for this purpose. . .

4. I have an idea...but i dont know how to do it. . .
how about a User Defined Function. . .
i understand they can return a table

but how do i go through each row in a query in the UDF...e.g:

Create Procedure ....blah blah
AS
BEGIN
INSERT INTO @resultingtable
SELECT * FROM RELPRODS WHERE pdid1=@pdid or pdid2=@pdid
///////how do i go through this 1 row at a time and evaluate it?
END

if that is not clear enough, just say so and i will try and clear anything up!

5. i want to select pdid1 if pdid2=27 or pdid2 if pdid1=27
but i dont want to have both.....any ideas?
caution, the following is not tested
Code:
```select case
when pdid2=27 then pdid1
else pdid2
end as flipperoo
from relprods
where ( pdid1=27 or pdid2=27 )
and not ( pdid1=27 and pdid2=27 )```

6. Thanks! That worked great...unfortunately by the time i got it i had already found a different solution....much too complicated...
i created a user defined function that returns the one that does not equal 27 soo...
select
returnsmaller(27,pdid1, pdid2) as prodid

see what i mean?

anyway thank you very much for your help!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•