|
oguzkaygun
Yak Posting Veteran
53 Posts |
 Posted - 2009-02-24 : 08:59:54
|
| hi i have UserId(coulumname) in amagazalar (name of table) and i have UserId(coulumname) in urunlistesi (name of table)... UserId in magazalar can be only one name.. for example john, smith etc.. (there is no john,john in Userid.. there is only one John in Userid) but UserId in urunlistesi can be more than one name like john, john, john, smith, smith, smith..But when UserId in urunlistesi can be more than one, 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..."For example, my scenirio of tables is belowuserid in magazalar (coulumname, table name) userid in urunlistesi (coulumname, table name)John JohnSmith JohnBeckham JohnSmithSmithBechkamBeckhamResult only should be John and information of John(1 record) where there is John in urunlistesi....(where kategory=@kategory1 and kategory2=@kategory2) (There are a lot of records about John but Result should be only 1 record.. John and information of John)But when there is two John in urunlistesi, there is error message that i wrote above.. if there is one John in urunlistesi, i can see record in gridview1.. How can it work with a lot of John in urunlistesi ?How can i enter a lot of john or smith (UserId) for urunlistesi ? how can i run it ?hii have tried for FULL JOIN and JOIN. i didnt use Grop By or Distinct.. but i saw same error message. because there is two john(coulumname) in urunlistesi (table). what should i do ?Note: when i change my class.vb or my dataset.xsd, i wait 15 or more minute for build. So i cant reply soon. Sorry.What i need exactly ? i have two tables (amagazalar(UserId, Information as coulumname) and urunlistesi(UserId, category, categorychild, childchildcategory as coulumname))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 in 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 amagazalarelse 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 ?My join is below...Note : UserId dont have primary or else key at tableINNER JOIN urunlistesi as B on A.UserId = B.UserId Thanksmy stored procedure is below.. when i write outer instead of inner, i cant save it.. what should i do ?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 = @ilanturuand B.aktifilan = @aktifilanandSET NOCOUNT ONSET Statistics io ON */ASBEGIN ;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.kategori2FROM amagazalar outer JOIN urunlistesi on amagazalar.UserId = urunlistesi.UserIdwhere 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+@NumRowsEND RETURN |
|
inner Join