Unable to pull data from views

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 2000 Forums

Transact-SQL (2000)

Unable to pull data from views

Author

Topic

citizenzen
Starting Member

1 Post

Posted - 2007-08-15 : 10:10:15

Hello. I have the following views:

1) view_locateapprovMedia
2) view_shwApprovedDet

view_shwApprovedDet shows all approved requests, while
view_locateapprovMedia displays all barcodes that exists in view_shwApprovedDet.

The problem is that I need to display
the following output:

Request# (detailsID)- from view_shwApprovedDet
User - from view_shwApprovedDet
Barcode - from view_locateapprovMedia
ItemID- from view_locateapprovMedia
Library- from view_locateapprovMedia
Shelf#- from view_locateapprovMedia
Shelf- from view_locateapprovMedia

How can I get this data correctly? I tried a join, a union, everything, but nothing seems to work. Or do i need to revise my views completely?

view_locateapprovMedia comes from view_shwApprovedDet and view_barcodes.

view_shwApprovedDet is a UNION statement.

Here's the sql query for the view_locateapprovMedia VIEW:

SELECT ItemID, Barcode, ShowID AS ShowNum, Show, MusicID AS MusicNum, ARTIST, TITLE AS Video, ElementNumber AS Element, ElementShow, Library, 
[Shelf Number], [Shelf Name]
FROM         dbo.view_showBarcodes
WHERE     EXISTS
(SELECT     * FROM          
view_shwApprovedDet
WHERE      view_shwApprovedDet.ShowID = view_showBarcodes.[fk program ID] OR                      view_shwApprovedDet.MusicID = view_showBarcodes.[fk video ID] OR                                                   view_shwApprovedDet.ElementNumber = view_showBarcodes.[ElementNumber])

CURRENT CODE FOR view_shwApprovedDet

SELECT     view_mediaDubsDetails.Record AS DetailsID, view_mediaDubsDetails.ElementNumber, view_mediaDubsDetails.fk_showID AS ShowID, 
                      view_mediaDubsDetails.Show, view_mediaDubsDetails.fk_musicID AS MusicID, view_mediaDubsDetails.Video, 
                      view_mediaDubsDetails.DateNeeded AS NeedsBy, view_mediaDubsDetails.Length AS Length, view_mediaDubsDetails.Copies AS Add1, 
                      view_mediaDubsDetails.UserID
FROM         view_mediaDubsDetails INNER JOIN
                      view_mediaDubRequests ON view_mediaDubRequests.Record = view_mediaDubsDetails.fk_mediaDubReq
UNION
SELECT     view_mediaCheckoutDetails.mediaCheckoutID AS DetailsID, view_mediaCheckoutDetails.ElementNumber, 
                      view_mediaCheckoutDetails.showID AS ShowID, view_mediaCheckoutDetails.Show, view_mediaCheckoutDetails.VideoNum AS MusicID, 
                      view_mediaCheckoutDetails.Video, view_mediaCheckoutReqs.dateRequested AS NeedsBy, view_mediaCheckoutDetails.Length AS Length, 
                      ISNULL(view_mediaCheckoutDetails.cpyCount, 0) AS Add1, view_mediaCheckoutDetails.UserID
FROM         view_mediaCheckoutDetails INNER JOIN
                      view_mediaCheckoutReqs ON view_mediaCheckoutDetails.fk_mediaCheckoutID = view_mediaCheckoutReqs.CheckoutRequest

Thanks in advance.

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-15 : 11:07:52

maybe there is not data.

try to break it into smaller parts and check the conditions in the where clause
there won't be much help without having access to data

Ashley Rhodes

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-08-15 : 11:24:42

Getting rid of the Views and using the tables directly should help performance.
The following looks as though it may produce the desired results:


SELECT AD.DetailsID AS RequestNum
    ,AD.UserID
    ,BC.Barcode
    ,BC.ItemID
    ,BC.Library
    ,BC.[Shelf Number]
    ,BC.[Shelf Name]
FROM view_shwApprovedDet AD
    JOIN dbo.view_showBarcodes BC
        ON AD.ShowID = BC.[fk program ID]
            OR AD.MusicID = BC.[fk video ID]
            OR AD.ElementNumber = BC.ElementNumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources