JOIN Problem

Question

Post reply

JOIN Problem

rajesha

SSC-Addicted

Points: 412
More actions
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.

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

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

GPSmith SSC Enthusiast Points: 114 More actions · Answer 1

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...

rajesha SSC-Addicted Points: 412 More actions · Answer 2

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

David Burrows SSC Guru Points: 65143 More actions · Answer 3

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.

peygham Right there with Babe Points: 757 More actions · Answer 4

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

rajesha SSC-Addicted Points: 412 More actions · Answer 5

rajesha

SSC-Addicted

Points: 412

July 28, 2003 at 1:14 am

#467207

thank you DavidBurrows it is working fine.