Post reply

what is the Perfect join for this tables output?

  • May 6, 2011 at 6:44 am

    #239566

    Hi,

    I have two tables :

    Table-1

    slno name

    1 vijay

    Table-2

    Roll

    A

    B

    C

    The required output is:

    slno name roll

    1 vijay A

    1 vijay B

    1 vijay C

    OR

    slno name roll

    1 vijay A

    1 vijay Null

    1 vijay Null

    Kindly Looking for a perfect query matching the requirement

    Regards,

    Vijayakumar N

  • May 6, 2011 at 7:00 am

    #1320974

    First Why you need two NULL's

    Why there only one column in table 2

    What is the excat requirement?

    What you are trying to achive from these.

    Have a clear idea of what you are asking so that the other guys who see's your requirement may know what is the problem and how can be it resolved.Be more specific on your requirement.

    Thanks
    Parthi

  • May 6, 2011 at 7:05 am

    #1320977

    There is no criterial for a join between those tables. It looks like you want a LEFT JOIN, but without joining criteria (a foreign key), you can't make that happen.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 6, 2011 at 7:09 am

    #1320983

    Using the left join i can achieve only one record... but i want two more record respect to the values 'B' and 'C' in the table-2

  • May 6, 2011 at 7:17 am

    #1320991

    vijayakumarn (5/6/2011)

    Using the left join i can achieve only one record... but i want two more record respect to the values 'B' and 'C' in the table-2

    Sounds like you're not doing the LEFT JOIN correctly. Do you also have criteria in the WHERE clause? That can cause the JOIN to act like an INNER join. Try moving that criteria to the JOIN information. Maybe try it as a RIGHT JOIN.

    Can you post the code. It's much easier to figure out what's going on with real structures and code.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 6, 2011 at 7:29 am

    #1321003

    you can use full Join to get the result

  • May 6, 2011 at 7:30 am

    #1321004

    table -1

    vijay BE ECE

    table-2

    vijay ECE a

    vijay CSE b

    vijay MEC c

    now i want

    vijay BE ECE a

    vijay BE CSE b

    vijay BE MEC c

    OR

    vijay BE ECE a

    vijay BE CSE null

    vijay BE MEC null

    This is what exactly

  • May 6, 2011 at 7:35 am

    #1321009

    select tab2.name from

    Tab1 CROSS JOIN tab2

  • May 6, 2011 at 9:58 am

    #1321129

    vijayakumarn (5/6/2011)

    table -1

    vijay BE ECE

    table-2

    vijay ECE a

    vijay CSE b

    vijay MEC c

    <snip>

    Are we to assume that each table has three columns or just one? Here is a link that might help you prepare your question DDl, DML and Expected output:

    http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply