SQl 2008 R2 Help

Gibbs · 2 Nov 2012 at 17:15

Am not to great on my SQL I have to admin so am coming here for some help.

I have a WCF web service that takes an XML call and returns an XML response containing data pulled from a DB for display to a user. This is a XML based event list pretty much.

There are a right few tables with the DB and I connect to them all to get data from each one. I am trying now to create a SQL "View" so when I run it, it will show me the same info as the web services pulls.

I just cant get the ****er to work. Below is the script I am using to select a load of tables

Code:

SELECT     dbo.Fixture.FixtureId AS EventId, dbo.Fixture.StartTime, dbo.Fixture.EndTime, dbo.Fixture.Information, dbo.Fixture.Description, 
                      dbo.Fixture.DataProviderId AS DataSourceId, dbo.Fixture.DataProviderItemId AS DataSourceItemId, dbo.Fixture.CompetitionId, dbo.Fixture.SeasonId, 
                      dbo.Fixture.RoundId, dbo.Fixture.GroupId, dbo.Fixture.CreatedTimestamp AS Created, dbo.Fixture.ModifiedBy, dbo.Fixture.Status, 
                      ISNULL(dbo.WTScheduledEvent.WTScheduledEventId, 0) AS WTScheduledEventId, ISNULL(dbo.WTScheduledEvent.CobainChannelId, 235) AS CobainChannelId, 
                      dbo.Competition.CompetitionName AS Competition, dbo.Competition.SportId, dbo.Competition.DisplayOrder, dbo.Round.RoundName AS Round, 
                      dbo.[Group].GroupName AS [Group], dbo.Season.SeasonName AS Season, ISNULL(dbo.Channel.ChannelName, 'Not Set') AS ServerName, 
                      ISNULL(dbo.WTScheduledEvent.DecoderId, 112) AS DecoderId, ISNULL(dbo.BroadcastSource.Source, 'Not Set') AS Source, dbo.Sport.DisplayName AS Sport, 
                      CONVERT(DATETIME, CONVERT(varchar(8), dbo.Fixture.StartTime, 112)) AS EventDate, dbo.EventCompetitors.Competitor2, dbo.EventCompetitors.Competitor1, 
                      dbo.Channel.PackageId, dbo.Package.PackageName AS Package, ISNULL(dbo.WTScheduledEvent.Verified, 0) AS Verified, ISNULL(dbo.WTScheduledEvent.Cancelled, 
                      0) AS Cancelled, ISNULL(dbo.WTScheduledEvent.InternalSchedule, 0) AS InternalSchedule, CASE WHEN SourceId = 427897 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) 
                      END AS StadiumFeed, dbo.WTScheduledEvent.Notes, dbo.Competition.AreaId, dbo.Area.Region, dbo.Area.ISOAlpha2Code, ISNULL(dbo.Fixture.Protected, 0) 
                      AS Protected, ISNULL(dbo.WTScheduledEvent.RedirectId, 235) AS RedirectId, dbo.Fixture.ModifiedBy AS FixtureModifiedBy, 
                      dbo.WTScheduledEvent.ModifiedBy AS WTScheduledEventModifiedBy, Archive.dbo.Recording.FileName, dbo.Round.StartDate, dbo.Round.EndDate
FROM         dbo.Fixture LEFT OUTER JOIN
                      dbo.WTScheduledEvent ON dbo.Fixture.FixtureId = dbo.WTScheduledEvent.FixtureId INNER JOIN
                      dbo.Competition ON dbo.Fixture.CompetitionId = dbo.Competition.CompetitionId INNER JOIN
                      dbo.[Group] ON dbo.Fixture.GroupId = dbo.[Group].GroupId INNER JOIN
                      dbo.Round ON dbo.Fixture.RoundId = dbo.Round.RoundId INNER JOIN
                      dbo.Season ON dbo.Fixture.SeasonId = dbo.Season.SeasonId LEFT OUTER JOIN
                      dbo.BroadcastSource ON dbo.WTScheduledEvent.SourceId = dbo.BroadcastSource.BroadcastSourceId LEFT OUTER JOIN
                      dbo.Channel ON dbo.WTScheduledEvent.CobainChannelId = dbo.Channel.ChannelId INNER JOIN
                      dbo.Sport ON dbo.Competition.SportId = dbo.Sport.SportId INNER JOIN
                      dbo.EventCompetitors ON dbo.Fixture.FixtureId = dbo.EventCompetitors.eventId LEFT OUTER JOIN
                      dbo.Package ON dbo.Channel.PackageId = dbo.Package.PackageId INNER JOIN
                      dbo.Area ON dbo.Competition.AreaId = dbo.Area.AreaId INNER JOIN
                      Archive.dbo.Recording ON dbo.Channel.ChannelId = Archive.dbo.Recording.ChannelID
WHERE     (dbo.Round.StartDate = CONVERT(DATETIME, '2012-10-12 00:00:00', 102)) AND (dbo.Round.EndDate = CONVERT(DATETIME, '2012-10-12 00:00:00', 102))

Any thing you guys can see am missing would be great. As it stands the above query returns no results. However If I remove the recording table it works fine.

Cheers in advance

GravyMonster · 2 Nov 2012 at 17:33

Without having access to your data or schema it's going to be near impossible for us to pin down the problem with that behemoth. If you don't get a syntax error the problem will be with the data... somewhere.

Mr^B · 2 Nov 2012 at 18:49

Hideous mash of inner and left joins in there.

What you need to do is take out the where clause and see if that returns any results.

Then you need to do a select * from (instead of that list) and add the joins in one at a time until you get no results. The table you last joined is responsible for you having 0 results...or is one of the ones responsible...

It's either going to be the where clause or one of the inner joins.

Gibbs · 2 Nov 2012 at 19:42

reason for the joins is cause stuff is stored in multiple tables. It just nuts whoever designed this like this, am just coming in after.

SQL inst my strong point at all. the WCF service can pull data no problem. this is to test what the WCF service is giving me to what is in the db.

Ill step over these one by one and see what happens

Shad · 2 Nov 2012 at 20:04

Gibbs said:
It just nuts whoever designed this like this

It's called normalisation and it's bloody important in a relational database! But as a software developer you knew that already, right?

As above, select * from fixture without the where clause and join each additional table one by one. Any tables that may contain matching data should be left joined with your primary table, and any tables that must contain matching data you can inner join, and that's probably where the problem will be. You can make it a lot more complicated than that but 9 times out of 10 it doesn't need to be.

Wizardskills · 11 Nov 2012 at 21:02

Shad said:
It's called normalisation and it's bloody important in a relational database! But as a software developer you knew that already, right?

As above, select * from fixture without the where clause and join each additional table one by one. Any tables that may contain matching data should be left joined with your primary table, and any tables that must contain matching data you can inner join, and that's probably where the problem will be. You can make it a lot more complicated than that but 9 times out of 10 it doesn't need to be.

100% this.

That code looks like it has been made in the Query Builder in the Management Studio. This always adds in these LEFT OUTER JOINS which are terrible.

As others have said, there are a couple of steps you can do here:

Remove the where clause
Comment out each table join one by one till you get data. When you do you know where the problem is.

However, I would really re-write the whole thing as it will be horrendous in terms of performance, especially if there is a lot of data in the database.