one-to-many relationship select query

  • Hello to all,

    I am a having trouble in querying one-to-many relationship. let me describe the scenario here. my table structure is like this.

    Table A

    A_IDF_NameM_NameL_Name

    1ABC

    2XYZ

    Table B

    B_IDA_IDCityStateZip

    11xxxTN37013

    21yyyTX37012

    Now i want to select all the rows from Table A and only first respective row from Table B.

    I appreciate all the help...

  • SELECT *

    FROM TABLE_A AS A

    LEFT JOIN TABLE_B AS B ON A.A_ID = B.A_ID

    [/code]

    Is that what you're looking for?

  • helpdesk-1100431

    First of all to get tested answers please post your table definitions along with sample data as outlined in the first link in my signature block. It would be something like the following:

    CREATE Table #A (A_ID INT,F_Name VARCHAR(2),M_Name VARCHAR(2),L_Name1 VARCHAR(2))

    INSERT INTO #A

    SELECT 1,'A','B','C'UNION ALL

    SELECT 2,'X','Y','Z'

    CREATE Table #B(B_ID INT,A_ID INT,City VARCHAR(5),State VARCHAR(2),Zip VARCHAR(10))

    INSERT INTO #B

    SELECT 1,1,'xxx','TN','37013' UNION ALL

    SELECT 2,1,'yyy','TX','37012'

    Now if the above is correct, then it would have been easy for Mke4King to test his suggested solution:

    SELECT *

    FROM TABLE_A AS A

    LEFT JOIN TABLE_B AS B ON A.A_ID = B.A_ID

    Which produces:

    A_IDF_NameM_NameL_Name1B_IDA_IDCityStateZip

    1ABC11xxxTN37013

    1ABC21yyyTX37012

    2XYZNULLNULLNULLNULLNULL

    Now is MIke4King's solution the one you are looking for? If not please post again and some one will be able to give you a tested solution.

    Another question. What defines the first row in Table B? Remember there is no explicit guarantee that rows return by a SELECT statement are returned in the order in which the rows were inserted.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Hello,

    SELECT *

    FROM TABLE_A AS A

    LEFT JOIN TABLE_B AS B ON A.A_ID = B.A_ID

    is not something i am looking for. as this query repeats the value from table A.

    I do not want to repeat my master information from Table A. And as to answer

    Another question. What defines the first row in Table B? Remember there is no explicit guarantee that rows return by a SELECT statement are returned in the order in which the rows were inserted.

    We can have first row from Table B or who's create date time stamp is first.

    does it make sense?.?

    Thanks for makin efforts for me.

  • who's create date time stamp is first.

    I revised table B by adding a column and an additional vaue of A_ID:

    CREATE Table #B(B_ID INT,A_ID INT,City VARCHAR(5),State VARCHAR(2),Zip VARCHAR(10),Entered DATETIME)

    INSERT INTO #B

    SELECT 1,1,'xxx','TN','37013',GETDATE() UNION ALL

    SELECT 2,1,'yyy','TX','37012',GETDATE() - 40 UNION ALL

    SELECT 2,2,'aaa','UT','44030', GETDATE() - 5

    Then try this code:

    ;with numbered as(SELECT rowno=row_number() over

    (partition by A_id order by entered ASC),A_Id,B_Id,city,state,zip,entered from #B) --

    SELECT A.F_name,A.M_Name,A.L_Name1, N.A_id,N.City, N.State, N.Zip,N.Entered

    FROM numbered AS N

    INNER JOIN #A AS A ON A.A_id = N.A_id WHERE rowno = 1

    Results:

    F_nameM_NameL_Name1A_idCityStateZipEntered

    ABC1yyyTX370122010-06-21 11:42:13.187

    XYZ2aaaUT440302010-07-26 11:42:13.187

    If this is not what you require please repost listing what I missed, or how I misundersood your requirements.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • helpdesk-1100431 (7/30/2010)


    Hello to all,

    I am a having trouble in querying one-to-many relationship. let me describe the scenario here. my table structure is like this.

    Table A

    A_IDF_NameM_NameL_Name

    1ABC

    2XYZ

    Table B

    B_IDA_IDCityStateZip

    11xxxTN37013

    21yyyTX37012

    Now i want to select all the rows from Table A and only first respective row from Table B.

    I appreciate all the help...

    There may be a prettier way, but this should accomplish what you want:

    SELECT *

    FROM Table_A a

    LEFT OUTER JOIN

    (SELECT B_ID,A_ID,City,State,Zip

    FROM Table_B b

    INNER JOIN

    (SELECT A_ID,bid=(min B_ID)

    FROM Table_B

    GROUP BY A_ID) as inner

    ON inner.bid=b.B_ID) as outer

    ON a.A_ID=outer.A_ID

  • Nevyn changed my create table statements to use your table names and ran your code:

    SELECT *

    FROM Table_A a

    LEFT OUTER JOIN

    (SELECT B_ID,A_ID,City,State,Zip

    FROM Table_B b

    INNER JOIN

    (SELECT A_ID,bid=(min B_ID)

    FROM Table_B

    GROUP BY A_ID) as inner

    ON inner.bid=b.B_ID) as outer

    ON a.A_ID=outer.A_ID

    Result: Msg 102, Level 15, State 1, Line 7

    Incorrect syntax near 'B_ID'.

    Wondering what I did incorrectly?

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • bitbucket-25253 (7/31/2010)


    Nevyn changed my create table statements to use your table names and ran your code:

    SELECT *

    FROM Table_A a

    LEFT OUTER JOIN

    (SELECT B_ID,A_ID,City,State,Zip

    FROM Table_B b

    INNER JOIN

    (SELECT A_ID,bid=(min B_ID)

    FROM Table_B

    GROUP BY A_ID) as inner

    ON inner.bid=b.B_ID) as outer

    ON a.A_ID=outer.A_ID

    Result: Msg 102, Level 15, State 1, Line 7

    Incorrect syntax near 'B_ID'.

    Wondering what I did incorrectly?

    You didn't, I did.

    I didnt test it or debug (wasnt at a sql server machine). Had some resrved words for alias names and incorrect brackets. Try the below

    CREATE Table #Table_A (A_ID INT,F_Name VARCHAR(2), M_Name VARCHAR(2),L_Name1 VARCHAR(2))

    INSERT INTO #Table_A

    SELECT 1,'A','B','C'UNION ALL

    SELECT 2,'X','Y','Z'

    CREATE Table #Table_B(B_ID INT,A_ID INT, City VARCHAR(5),State VARCHAR(2),Zip VARCHAR(10))

    INSERT INTO #Table_B

    SELECT 1,1,'xxx','TN','37013' UNION ALL

    SELECT 2,1,'yyy','TX','37012'

    SELECT *

    FROM #Table_A a

    LEFT OUTER JOIN

    (SELECT B_ID,b.A_ID,City,State,Zip

    FROM #Table_B b

    INNER JOIN

    (SELECT A_ID,bid=min (B_ID)

    FROM #Table_B

    GROUP BY A_ID) as derived2

    ON derived2.bid=b.B_ID) as derived1

    ON a.A_ID=derived1.A_ID

    DROP TABLE #Table_A

    DROP TABLE #Table_B

    My only comment on your solution is that it won't show a ROW of A if there isnt a matching row in B. Can be fixed by making the join right outer and putting the where condition on COALESCE(rowno,1).

    I should also add that mine doesnt work with your amended test data because you B_ID wasnt unique in it

  • Nevyn

    I asked the OP a question :

    Another question. What defines the first row in Table B? Remember there is no explicit guarantee that rows return by a SELECT statement are returned in the order in which the rows were inserted.

    OP's reply:

    We can have first row from Table B or who's create date time stamp is first.

    In response to that I added a column to Table B

    CREATE Table #B(B_ID INT,A_ID INT,City VARCHAR(5),State VARCHAR(2),Zip VARCHAR(10),Entered DATETIME)

    INSERT INTO #B

    SELECT 1,1,'xxx','TN','37013',GETDATE() UNION ALL

    SELECT 2,1,'yyy','TX','37012',GETDATE() - 40 UNION ALL

    SELECT 2,2,'aaa','UT','44030', GETDATE() - 5

    at this point I got lost in the detail .... Wondering how you would modify your T-SQL to select based on earliest date?

    Thanks for the advice on using a RIGHt OUTER JOIN and COALESCE(rowno,1).

    will test that tomorrow ...

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Here's how I would do it with the datestamp field:

    CREATE Table #Table_A (A_ID INT,F_Name VARCHAR(2), M_Name VARCHAR(2),L_Name1 VARCHAR(2))

    INSERT INTO #Table_A

    SELECT 1,'A','B','C'UNION ALL

    SELECT 2,'X','Y','Z'

    CREATE Table #Table_B(B_ID INT,A_ID INT,City VARCHAR(5),State VARCHAR(2),Zip VARCHAR(10),Entered DATETIME)

    INSERT INTO #Table_B

    SELECT 1,1,'xxx','TN','37013',GETDATE() UNION ALL

    SELECT 2,1,'yyy','TX','37012',GETDATE() - 40 UNION ALL

    SELECT 2,2,'aaa','UT','44030', GETDATE() - 5

    SELECT *

    FROM #Table_A a

    LEFT OUTER JOIN

    (SELECT B_ID,b.A_ID,City,State,Zip

    FROM #Table_B b

    INNER JOIN

    (SELECT A_ID,bid=min (Entered)

    FROM #Table_B

    GROUP BY A_ID) as derived2

    ON derived2.bid=b.Entered AND derived2.A_ID =b.A_ID) as derived1

    ON a.A_ID=derived1.A_ID

    DROP TABLE #Table_A

    DROP TABLE #Table_B

    Basically the same thing. Note that adding the AND derived2.A_ID =b.A_ID to my original will also fix the issue of duplicate B_IDs throwing it off.

    This does fail on the case where 2 records of B with the same A_ID have identical timestamps. Id need more nesting to get around that (min datetime then min ID), so I think your row_number solution probably makes more sense for the date issue.

  • Nevyn Posted Yesterday @ 11:27 PM

    Basically the same thing. Note that adding the AND derived2.A_ID =b.A_ID to my original will also fix the issue of duplicate B_IDs throwing it off.

    Not near a machine with SQL Server, but much thanks for teaching the OP AND myself. I do appreciate your efforts to teach both of us some truly neat T-SQL.

    Now I wish the OP had supplied us with the complete table definitions (indexes) etc., so we could look at the execution plans for both suggested solutions, then this would have been a great learning experience for the OP and myself.

    Again I thank you for all your effort, and I am sure the OP shares my gratitude.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Thanks you alll...

    Above one is just perfect for my problem. And i will make my changes according to specification.

    Thanks for making efforts for me... I appreciate all the work thanks again.... 🙂

  • Thanks all to make effort for me.

    This one does work for me just perfect.

    Once again thank you all and i really appreciate all the help... with 🙂

Viewing 13 posts - 1 through 12 (of 12 total)

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