Results 1 to 5 of 5
  1. #1
    Join Date
    May 2008
    Posts
    97

    Unanswered: Join On only '1' Match

    Hi,

    Please see the table structure below.

    Using my temp table (#Items) I would like to add "VendorName" to my current Temp Table. The problem with a JOIN appears that I seem to get more rows returned than in my #ITEMS temp table! When I only want 1 match of the non-unique ITEMSID column. Is there a way to do this??

    #Items (Temp table)

    ID | ItemsID(FK ItemDesc) | COUNT
    1 | L021 | 234
    2 | L022 | 223
    3 | L023 | 235
    4 | L024 | 234
    ItemDesc

    ID | ItemsID | VendorID
    1 | L021 | 1
    2 | L022 | 2
    3 | L022 | 2
    4 | L023 | 3
    5 | L024 | 3
    Vendors
    ID | VendorName
    1 | Jo corp.
    2 | Bob corp.
    3 | Mike corp.

    ============

    Expected Results

    ID Items(FK ItemDesc) | COUNT | VendorName
    1 | L021 | 234 | Jo corp.
    2 | L022 | 223 | Bob corp.
    3 | L023 | 235 | Mike corp.
    4 | L024 | 234 | Mike corp.
    Any help would be appreciated!
    Last edited by Eric the Red; 03-28-12 at 20:38.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Add DISTINCT to your query.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    May 2008
    Posts
    97
    I do and still more rows returned than in my temp table.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    NO..you gotta READ These things

    Let us ask you

    Why Bob and Not Mike?

    You wan the "first"one?

    Based on MIN(ID) perhaps?
    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.

  5. #5
    Join Date
    May 2008
    Posts
    97
    Quote Originally Posted by Brett Kaiser View Post
    NO..you gotta READ These things

    Let us ask you

    Why Bob and Not Mike?

    You wan the "first"one?

    Based on MIN(ID) perhaps?
    I really just want the first match added to my temp table - so sure based on min(id) is good.

Posting Permissions

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