SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: 2 Columns as 1?

  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    NJ
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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?
    signature

  2. #2
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    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;"
    Set RS = Server.createobject("ADODB.Recordset")
    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. #3
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    NJ
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. . .
    signature

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    NJ
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!
    signature

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    NJ
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!
    signature


Bookmarks

Posting Permissions

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