no duplications in inner join / left join / right join query / JOIN HELP

Discussion in 'microsoft.public.sqlserver.programming' started by shripaldalal, Mar 3, 2007.

  1. shripaldalal

    shripaldalal Guest

    Hi,

    I have two tables

    table1:
    ========================
    name sirname
    ===== ======
    shripal dalal
    deepak dalal

    table2:
    ========================
    name sirname
    ===== ======
    ramesh dalal

    now if i do a left join/inner join/right join (watever, i will take
    left join as an example):

    select table1.name, table2.name from table1 left join table2 on
    table1.sirname = table2.sirname i get:

    table1.name table2.name
    ========= ==========
    shripal ramesh
    deepak ramesh

    now the above is wat i dont want.........

    i want something like this:

    table1.name table2.name
    ========= ==========
    shripal ramesh
    deepak <blank>

    basically i just want to CONCATENATE TWO RECORDSETS....

    something like:

    select table1.name from table1 + select table2.name from table2 but
    where sirname is equal in both tables

    is this possible ?

    regards,
    shripal dalal.
     
    shripaldalal, Mar 3, 2007
    #1
    1. Advertisements

  2. shripaldalal

    David Portas Guest

    You've left a few questions unanswered, such as: What are the keys?
    What if there is more than one person with the same name? Here's one
    possibility:

    SELECT t1.name, t2.name
    FROM table1 AS t1
    LEFT JOIN table2 AS t2
    ON t1.sirname = t2.sirname
    AND NOT EXISTS
    (SELECT *
    FROM table1
    WHERE sirname = t1.sirname
    AND name > t1.name);

    --
    David Portas, SQL Server MVP

    Whenever possible please post enough code to reproduce your problem.
    Including CREATE TABLE and INSERT statements usually helps.
    State what version of SQL Server you are using and specify the content
    of any error messages.

    SQL Server Books Online:
    http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
    --
     
    David Portas, Mar 3, 2007
    #2
    1. Advertisements

  3. shripaldalal

    shripaldalal Guest

    hi david,

    thanks for the reply.
    basically what i need this for is:

    there are two tables:

    table1 is a master table, table2 is a detail table.

    table1's primary_id is present in details_id of table2

    table1 has only 1 record
    table2 could have many records, usually upto 6 or seven

    the problem is that when i inner join/left join/right join the above
    tables in any way.......

    the records of table1 repeat next to all records of table2, i know
    this is a normal behaviour for any join

    what i want is:

    select table1.name, table2.orders from table1, table2 where table1.id
    = table2.details

    i get something like this:

    name order
    ==== ========
    shripal tv
    shripal phone
    shripal fridge
    shripal dvd
    shripal oven
    shripal laptop

    instead wat i want is:

    name order
    ==== ========
    shripal tv
    <blank> phone
    <blank> fridge
    <blank> dvd
    <blank> oven
    <blank> laptop

    is such a thing possible ? it could really save a lot of work.

    regards,
    shripal.
     
    shripaldalal, Mar 3, 2007
    #3
  4. shripaldalal

    ML Guest

    ML, Mar 3, 2007
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.