there is problem with two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

there is problem with two tables

Author

Topic

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2009-03-02 : 06:08:04

hi

i have two tables (amagazalar(UserId, Information as coulumname) and urunlistesi(UserId, category, categorychild, childchildcategory as coulumname))

amagazalar (table)

UserId Information

1 jfdkjfd

2 dkfdf

3 jfdkjdj

urunlistesi (table)

UserId kategori kategori1 kategori2

1 computer hardisk 80 Gb

1 computer hardisk 160 Gb

2 phone Samsung ...........

2 phone Nokia ..........

i want to joing UserId=UserId

for example there is one record in amazalar(table) that UserId is John. and there are alot of records in urunlistesi(table) that Userid are John. i have three categories.. category, childcategory, childchildcategory in urunlistesi(table)..

so when i click category or childcategory or childchildcategory, there are records that userid are John in urunlistesi.. but i should see only John and information of John from amagazalar at gridview1 if there are John in urunlistesi..

when i click cagetory, maybe there are 5 John in urunlistesi. but i should see only one John and information of John from amagazalar

else example.. when i click categorychild, there are 3 John and there are 5 Smith in urunlistesi(table).. but i should see 1 John and information of John and i should see 1 Smith and information of Smith from amagazalar(table)..

How can i do it ?

i wrote below Storedprocedure but it works wrongly.. Storedprocedure should be work for 1.000.000 records in urunlistesi and 100.000 records in amagazalar

ALTER PROCEDURE dbo.magazalarliste
@aktifmagaza varchar(5),

@ilanturu varchar(18),
@aktifilan varchar(5),

@kategori varchar(23),
@kategori1 varchar(48),

@kategori2 varchar(55), @StartRowIndex INT,
@NumRows INT

AS

begin

;with Listurunlistesi as (

SELECT ROW_NUMBER() OVER (order by UserId asc)

AS Row, UserId

FROM urunlistesi

where ilanturu = @ilanturu

and aktifilan = @aktifilan

and kategori = @kategori

and ((@kategori1 is null) or (kategori1 = @kategori1))

and ((@kategori2 is null) or (kategori2 = @kategori2))

group by UserId
)

SELECT ROW_NUMBER() OVER (ORDER BY amagazalar.[onsira], amagazalar.[magazanumarasi] desc)

AS Roww, amagazalar.magazanumarasi, amagazalar.UserId, amagazalar.madi, amagazalar.maciklama, amagazalar.resim1, amagazalar.urunsayisi, amagazalar.[onsira]

FROM amagazalar

inner join Listurunlistesi on Listurunlistesi.UserId = amagazalar.UserId

WHERE aktifmagaza = @aktifmagaza

and Row between @StartRowIndex and @StartRowIndex+@NumRows

end

RETURN

My second storedprocedure is below..But when UserId in urunlistesi can be more than one in urunlistesi (table), there is error message that "Limitations cant be enable.. One row or more row is not Null..One row or more row have unsame, foreign key limitations..."

ALTER PROCEDURE dbo.magazalarliste
@kategori varchar(23),

@kategori1 varchar(48),
@kategori2 varchar(55),

@StartRowIndex INT,
@NumRows INT

/*@aktifmagaza varchar(5),

@ilanturu varchar(18),

@aktifilan varchar(5),

A.aktifmagaza = @aktifmagaza
and B.ilanturu = @ilanturu

and B.aktifilan = @aktifilan

and

SET NOCOUNT ON

SET Statistics io ON

*/

AS

BEGIN

;WITH ListEntries AS (

SELECT ROW_NUMBER() OVER (ORDER BY amagazalar.[onsira], amagazalar.[magazanumarasi] desc)
AS Row, amagazalar.magazanumarasi, amagazalar.UserId, amagazalar.madi, amagazalar.maciklama, amagazalar.resim1, amagazalar.urunsayisi, amagazalar.onsira, amagazalar.aktifmagaza,

urunlistesi.ilanturu, urunlistesi.aktifilan, urunlistesi.kategori, urunlistesi.kategori1, urunlistesi.kategori2

FROM amagazalar

outer JOIN urunlistesi on amagazalar.UserId = urunlistesi.UserId

where urunlistesi.kategori = @kategori

and ((@kategori1 is null) or (urunlistesi.kategori1 = @kategori1))

and ((@kategori2 is null) or (urunlistesi.kategori2 = @kategori2))group by amagazalar.magazanumarasi, amagazalar.UserId, amagazalar.madi, amagazalar.maciklama, amagazalar.resim1, amagazalar.urunsayisi, amagazalar.onsira, amagazalar.aktifmagaza, urunlistesi.ilanturu, urunlistesi.aktifilan, urunlistesi.kategori, urunlistesi.kategori1, urunlistesi.kategori2

)

SELECT *

FROM ListEntries

WHERE Row between @StartRowIndex and @StartRowIndex+@NumRows

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 13:13:26

what all details you want to retrieve along with name? can you post your sample output?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources