Results 1 to 4 of 4

Thread: merge statement

  1. #1
    Join Date
    Dec 2003
    Location
    san jose
    Posts
    3

    Unanswered: merge statement

    Hello,
    I have two tables: Orders and Appoitment. Each order can have up to 2 appointments. Now, I need a SELECT statement that gives me this:

    ORDER APPT
    1 appt1 appt2
    2 appt1 appt2

    and not this:

    ORDER APPT
    1 appt1
    1 appt2
    2 appt1
    2 appt2

    In other words, I want to merge the two appointments for each order. I tried using the merge statement but it does not work. Tried to google but saw nothing. My database is SQL server. Please help. Thanks
    Last edited by phamtranquocvie; 12-28-03 at 06:50.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You tried the MERGE statement?

    Are you doing OLAP?

    And how, in a 1 to many relationship did you guarentee that it can be only 2 values?

    CONSTRAINTS?

    In any case ytou want something like this...

    SELECT *
    FROM Orders o
    LEFT JOIN APT a1
    ON o.key = a1.key
    AND AptCd = 'APT1'
    LEFT JOIN APT a2
    ON o.key = a2.key
    AND AptCd = 'APT2'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I second Brett's concern. How do you guarantee only two appointments per order? If there is a field in each record that indicates the "type" of appointment, such as 'Initial', 'FollowUp', then you should consider using a cross-tab query because it is more flexible. If there is not, then Brett's solution is probably the only way to go.

    blindman

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about this...you'd need a cursor....

    DECLARE @x varchar(256), @y varchar(8000)

    SELECT @x = OrderId, @y = @y + ISNULL(','+Apt,'') FROM Table...

    or something like that..

    That way you can string together n appointments...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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