INNER JOIN - SQL Server Forums

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

Other SQL Server 2008 Topics

INNER JOIN

Author

Topic

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2009-02-24 : 07:20:20

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 below

userid in magazalar (coulumname, table name) userid in urunlistesi (coulumname, table name)

John John

Smith John

Beckham John

Smith

Smith

Bechkam

Beckham

Result 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 ?

hi

i 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 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 ?

My join is below...Note : UserId dont have primary or else key at table

INNER JOIN urunlistesi as B on A.UserId = B.UserId

Thanks

my 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 = @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

RETURN

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-02-24 : 09:03:09

dup...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=120430

Em

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2009-03-02 : 06:35:23

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources