Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    7

    Unanswered: Querys and duplicated fields

    Hello,

    I've got a trouble with my query and left outer joins. I've got 2 tables, table A and B, both have the same record called ID. I used this query:
    SELECT *
    FROM A LEFT OUTER JOIN B ON A.ID = B.ID

    This is OK and works fine, my trouble comes when i have a duplicated ID on both A and B. Instead of return 2 fields, it returns me 4.

    Is there some way to force SQl server to return only the first founded on B, but the 2 duplicated IDs on the A table?

    Thanks for all.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Read the sticky at he top of the forum and give us some more information
    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
    Oct 2005
    Posts
    7
    sorry.

    My database is like:
    Table A:
    ID -> nvarchar 8
    MODEL -> nvarchar 20
    COLOR -> nvarchar 6

    Table B:
    ID -> nvarchar 8
    COM -> nvarchar 50

    The tables names are really A and B. It's only some experiment i'm doing at home. I don't use index in neither 2 tables. Basically thats what it works:
    table A contains Id, model, and color of af pencil. And table B contains the ID of the pencil in question, and COM some coments about de pencil in question.

    I use ID fields to search in 2 tables, to practice JOINS. Inserting and quering i realise if for example i have:

    Table A:
    ID MODEL COLOR
    1 BIC BLUE
    1 BIC BLACK

    and in table B:
    ID COM
    1 Good color.
    1 Don't like the color

    And i do this query:
    SELECT * FROM A LEFT OUTER JOIN B ON A.ID = B.ID where a.id='1'

    sql server returns:
    ID MODEL COLOR ID COM
    1 BIC blue 1 Good color
    1 BIC black 1 Good color
    1 BIC blue 1 don't like the color
    1 BIC black 1 don't like the color

    My question is: is the some way to force sql server to return only the repeated fields in table A, some like that:

    ID MODEL COLOR ID COM
    1 BIC blue 1 Good color
    1 BIC black 1 Good color

    If it's more easy to undertand, insted of table B of above, use this table B:
    new table B:
    ID COM
    1 Good color
    1 Good color

    so i want server to returns only the 2 repeated rows on A table:
    ID MODEL COLOR ID COM
    1 BIC blue 1 Good color
    1 BIC black 1 Good color


    Hope now its more clear.

    Thank for all.

  4. #4
    Join Date
    Oct 2005
    Posts
    5
    I'm not sure why you need an outer join for this query. That aside, there are really two issues here and while my opinion is that they should be solved by fixing your tables, I'll answer them first before doing that.

    Given the first set of data in table B, what you're seeing from the query is exactly correct. Both entries in table B apply to both entries in table A, so of course you're going to get four results. If you only want to see "Good color" comments, add that to your query:

    Code:
    SELECT * 
    FROM A LEFT OUTER JOIN B ON A.ID = B.ID 
    where a.id='1'
        and b.com = 'Good color'
    Given the second set of data in table B, where the comment is duplicated and you want to filter out duplicates, you can use "SELECT DISTINCT":

    Code:
    SELECT DISTINCT * 
    FROM A LEFT OUTER JOIN B ON A.ID = B.ID 
    where a.id='1'
    Now the real solution as I see it is to fix your tables. Your comments in table B appear to apply not just to an id in table A, but an id + a color. So, you should redefine table B as:

    Code:
    create table B
    (
        id nvarchar(8)
        , color nvarchar(6)
        , com nvarchar(50)
    )
    and insert data like:

    Code:
    insert into b (id, color, com)
    select N'1', 'Blue', 'Good color'
    union
    select N'1', 'Black', 'Don''t like the color'
    Then you can query based on color and id:

    Code:
    SELECT a.id, a.model, a.color, b.com
    FROM A LEFT OUTER JOIN B ON A.ID = B.ID and a.color = b.color 
    where a.id='1'
    This gives you:

    Code:
    id       model                color  com
    ----------------------------------------
    1        BIC                  Black  Don't like the color
    1        BIC                  Blue   Good color
    Using your second set of data, you'd end up with:

    Code:
    id       model                color  com
    ----------------------------------------
    1        BIC                  Black  Good color
    1        BIC                  Blue   Good color
    I've purposely avoided other relevant questions (like, why are your IDs nvarchar? or why aren't your IDs unique? or why are your IDs nullable?), since you mentioned the goal here is to play around with different join types.

Posting Permissions

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