Post reply

JOIN Problem

  • July 24, 2003 at 12:54 am

    #81338

    i have 2 tables Table_main and Table_details

    Table_main contains only distinct records,For each record in this table there will be so many

    records in Table_details (one - many relation ship).

    i want to write a query which gives only that much records in Table_main but with corresponding

    records in Table_details (appended or in some other way)

    I tried with joins but same records in Table_main is repeating for each distinct record in

    Table_details.

    How can i solve this.Pl help me.

  • July 24, 2003 at 4:30 am

    #466754

    How do you want the records returned?

    Is it for a report output, therefore would just blanking out the repeated lines be acceptable, ie:

    ID....detail

    1.....Ball

    ......Bat

    ......Club

    2.....Swing

    ......Slide

    etc...

    If not, in what manner do you want the lines "Appended", Pipe delimited in one field for example?

    Both would require a storage temp table or table variable, and some while looping...

  • July 24, 2003 at 5:25 am

    #466759

    Eg.

    Table_main

    sno no

    1 12313

    2 98894

    Table_details

    Sno Item date

    1 TV 3/1/03

    1 Fridge 3/1/03

    2 Car 3/3/01

    2 Tape 4/4/01

    i want result like

    Sno no item1 date1 item2 date2

    1 12313 TV 3/1/03 Fridge 3/1/03

    2 98894 Car 3/3/01 Tape 4/4/01

    Thanks

  • July 24, 2003 at 5:57 am

    #466767

    You need to pivot your data, there are other threads on this forum that may help. The following is a possible solution.

    First create temp table containing details plus rowid and set rowid

    CREATE TABLE #temp_details (Sno int, Item varchar(20), [date] datetime, rowID int)

    INSERT INTO #temp_details SELECT Sno,Item,[date],0 FROM Table_details ORDER BY Sno

    DECLARE @rowID int,@Sno int

    SET @rowID = 0

    SET @Sno = 0

    UPDATE #temp_details SET @rowID = (CASE WHEN Sno = @Sno THEN @rowID + 1 ELSE 1 END),rowID = @rowID, @Sno = Sno

    If there is a max number of details per main (eg 2) then

    SELECT m.sno,m.no,

    ,MAX(CASE WHEN rowID = 1 THEN Item ELSE '' END) AS item1

    ,MAX(CASE WHEN rowID = 1 THEN [Date] ELSE '' END) AS date1

    ,MAX(CASE WHEN rowID = 2 THEN Item ELSE '' END) AS item2,

    ,MAX(CASE WHEN rowID = 2 THEN [Date] ELSE '' END) AS date2

    FROM Table_main m INNER JOIN #temp_details d ON d.sno = m.sno GROUP BY m.sno,m.no

    If there is a variable number of details per main then use dynamic sql (and all the associated problems)

    DECLARE @sql nvarchar(8000), @maxID int,@counter int

    SELECT @maxID = MAX(rowID) FROM #temp_details

    SET @sql = 'SELECT m.sno,m.no'

    SET @counter = 0

    WHILE (@counter < @maxID)

    BEGIN

    SET @counter = @counter + 1

    SET @sql = @sql + ',MAX(CASE WHEN rowID = '++CAST(@counter as varchar)+' THEN Item ELSE '''' END) AS item'+CAST(@counter as varchar)

    SET @sql = @sql + ',MAX(CASE WHEN rowID = '++CAST(@counter as varchar)+' THEN [Date] ELSE '''' END) AS date'+CAST(@counter as varchar)

    END

    SET @sql = @sql + ' FROM Table_main m INNER JOIN #temp_details d ON d.sno = m.sno GROUP BY m.sno,m.no'

    EXEC sp_executesql @sql

    Far away is close at hand in the images of elsewhere.
    Anon.

  • July 25, 2003 at 7:28 am

    #467039

    rajesha, I may not have understood your question correctly, but if you've got a one to many relationship, would a left join not work

  • July 28, 2003 at 1:14 am

    #467207

    thank you DavidBurrows it is working fine.

Viewing 6 posts - 1 through 5 (of 5 total)

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