How to do this? Part-2

  • I have a table A:

    StudIDRollNo

    AlphaD1234

    betaA1122

    charlieD1234

    bravoC1342

    tomB1964

    harryA1122

    and table B:

    RollNoSubjectCode

    D12341001

    A11224001

    D12342001

    C13425001

    B19646001

    A11223001

    I want to join the table A and B. For studID Alpha from table A i want the subjectcode 1001 only from table B and not want what other subjectcodes as you can see 'D1234' is repeated in table B.

  • The same comment applies as from your last thread. There is no guaranteed row order in SQL tables, therefore you cannot ask for 'the first occurrence' unless you define an order explicitly.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • ganeshkumar005 (8/24/2012)


    I have a table A:

    StudIDRollNo

    AlphaD1234

    betaA1122

    charlieD1234

    bravoC1342

    tomB1964

    harryA1122

    and table B:

    RollNoSubjectCode

    D12341001

    A11224001

    D12342001

    C13425001

    B19646001

    A11223001

    I want to join the table A and B. For studID Alpha from table A i want the subjectcode 1001 only from table B and not want what other subjectcodes as you can see 'D1234' is repeated in table B.

    I'm going to assume you want the lowest SubjectCode.

    --== SAMPLE DATA

    SELECT StudID, RollNo

    INTO #tableA

    FROM (VALUES('Alpha','D1234'),('beta','A1122'),('charlie','D1234'),('bravo','C1342'),

    ('tom','B1964'),('harry','A1122'))a(StudID, RollNo);

    --== SAMPLE DATA

    SELECT RollNo, SubjectCode

    INTO #tableB

    FROM (VALUES('D1234',1001),('A1122',4001),('D1234',2001),('C1342',5001),('B1964',6001),

    ('A1122',3001))a(RollNo, SubjectCode);

    --== ACTUAL SOLUTION

    SELECT tblA.StudID, tblA.RollNo, subQuery.SubjectCode

    FROM #tableA tblA

    CROSS APPLY (SELECT TOP 1 RollNo, SubjectCode

    FROM #tableB tblB

    WHERE tblA.RollNo = tblB.RollNo) subQuery;

    Also, take a look at this link about SQL Server ordering.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • both these posts looked way to much like homework;

    I don't think the OP will learn anything if we do the work for him, without letting him learn the techniques.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell - there could be always some lateral learnings. You write a script, I see how to write it. I am not into T-SQl by the way. And thanks for your help, everybody.

  • You can do it using CTE also.

    --========= Student table

    IF OBJECT_ID('tempdb..#Student') IS NOT NULL

    BEGIN

    DROP TABLE #Student

    END

    GO

    CREATE TABLE #Student

    (

    StudID VARCHAR(100),

    RollNo VARCHAR(100)

    )

    GO

    INSERT INTO #Student VALUES('Alpha', 'D1234')

    INSERT INTO #Student VALUES('beta', 'A1122')

    INSERT INTO #Student VALUES('charlie', 'D1234')

    INSERT INTO #Student VALUES('bravo', 'C1342')

    INSERT INTO #Student VALUES('tom', 'B1964')

    INSERT INTO #Student VALUES('harry', 'A1122')

    Go

    --========= StudentSubjects table

    IF OBJECT_ID('tempdb..#StudentSubjects') IS NOT NULL

    BEGIN

    DROP TABLE #StudentSubjects

    END

    GO

    CREATE TABLE #StudentSubjects

    (

    RollNo VARCHAR(100),

    SubjectCode VARCHAR(100),

    )

    GO

    INSERT INTO #StudentSubjects VALUES ('D1234', 1001)

    INSERT INTO #StudentSubjects VALUES ('A1122', 4001)

    INSERT INTO #StudentSubjects VALUES ('D1234', 2001)

    INSERT INTO #StudentSubjects VALUES ('C1342', 5001)

    INSERT INTO #StudentSubjects VALUES ('B1964', 6001)

    INSERT INTO #StudentSubjects VALUES ('A1122', 3001)

    Go

    --======= Solution:

    ;WITH MyCTE (RollNo, LowestSubjectCode)

    AS

    (

    SELECT RollNo, MIN (SubjectCode) AS LowestSubjectCode FROM #StudentSubjects GROUP BY RollNo

    )

    SELECTS.StudId,S.RollNo,MC.LowestSubjectCode

    FROM#Student S INNER JOIN MyCTE MC

    ONS.RollNo = MC.RollNo


    Sujeet Singh

  • i've put more than a few posts on the board here on SSC, so I've helped an aweful lot of people.

    ;

    the best way to learn is to try. if you posted a query that wasn't working, but showed effort, we could help you learn.

    I'm really afraid you'll just take the solutions here, hand them in, but not understand them;

    when the next question comes that is similar, you'll be stuck, because you didn't get a handle on hte original.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Agree with you.

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

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