Help on sql View - 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

General SQL Server Forums

New to SQL Server Programming

Help on sql View

Author

Topic

bplvid
Starting Member

45 Posts

Posted - 2012-06-11 : 11:35:03

I've the below view for grabbing the datas from mulitple tables and viewing it.

ALTER VIEW [dbo].[viewitems]
AS

(select distinct(r.itemID),

r.itemName,
P.productName,
k.keywordname as Keywords

from items r left outer join Products p on r.itemId = p.itemId
left outer join keywords k on k.itemid = r.itemid
)
union
(
(select distinct(r.itemID),

r.itemName,
P.productName,
k.keywordname as Keywords

from items r left outer join engProducts p on r.itemId = p.itemId
left outer join keywords k on k.itemid = r.itemid
)
union
(
(select distinct(r.itemID),

r.itemName,
P.productName,
k.keywordname as Keywords

from items r left outer join eqiProducts p on r.itemId = p.itemId
left outer join keywords k on k.itemid = r.itemid
)

GO

The resultant tables is as below

Itemname | Productname | keywordname

sample123 | content | available

sample123 | content | theme

sample123 | content | notapplicable

sample123 | testa | available

sample123 | testa | theme

sample123 | testa | notapplicable

sample123 | duplicate | available

sample123 | duplicate | theme

sample123 duplicate | notapplicable

It's shows replicated data's because I've productname corresponding to that particular itemid as content,testa,duplicate and keywords to that itemid as

available,theme,notapplicable.Please help me to refine my view.i tried creating another view for keyword with distinct keywords it works but can't use it in this existing view.How do I solve this problem?

Any help or suggestion is much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-11 : 15:11:43

so what should be output you want? can you post it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-06-11 : 15:25:09

quote:
Originally posted by visakh16

so what should be output you want? can you post it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I'm not sure whether it is possible in sql or should i do it in my code behind(front end) i need to display all datas, productname and keyword to appear once those related to particular itemid.

something like

Itemname Productname keywordname

?sample 123 content available

sample 123 testa theme

sample123 duplicate notapplicable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-11 : 15:29:16

how d o you determine keywordname to be related to a product? how is relationship setup in table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-06-11 : 16:36:23

quote:
Originally posted by visakh16

how d o you determine keywordname to be related to a product? how is relationship setup in table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sorry for the delayed reply.
I've items table,product table and keyword table all these tables has relationship table like itemproducttable with itemid and productid and similarly keyworditemtable with itemid and keywordid .Hope i din't confuse you.
For eg

itemproducttable (relationship table)

itemid productid

101 3403
101 5677
101 3456

145 5677
145 5667

340 2334
340 3403

keyworditemtable (relationship table)

itemid keywordid

101 344
101 235
101 456

145 344
145 455

340 235
340 666

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-11 : 16:55:19

and you want to return only first keyword corresponding to each product?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-06-12 : 10:46:03

quote:
Originally posted by visakh16

and you want to return only first keyword corresponding to each product?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

no,no I need all the keywords,productname.IS it possible in sql or can I call a view within a view or I sdhould handle it in my code behind using loops.Please advice.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 14:52:43

quote:
Originally posted by bplvid

quote:
Originally posted by visakh16

and you want to return only first keyword corresponding to each product?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

no,no I need all the keywords,productname.IS it possible in sql or can I call a view within a view or I sdhould handle it in my code behind using loops.Please advice.

then how do you want to show multiple keywords when you return distinct values for productname? obviously you wont be able to show all the associated keyword values when you make productname distinct unless you show them as a list. is that what you're looking at?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources