Totaly Joins-messed Up - 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 2000 Forums

SQL Server Development (2000)

Totaly Joins-messed Up

Author

Topic

hesta96
Starting Member

9 Posts

Posted - 2002-02-05 : 04:38:06

I have a problem that I would love to get som help on. I am making a system that produce news on the web. I have a good DB that holds the information in kind of objectoriented structure, but get a problem when I want to write a good question.

I have one table with the publishinginformation, one with the textinformation, one with the imageinformation and one with the styleinformation. I have different Units that write news and it is possible to have one picture to the newsarticle, but it is not always there. I am trying to write a sql-question that returns a RS with all information about all publishings from a unit for one day. It works if I dont want to add the imageinformation, if I do, I only get the ones that holds a image and I want a NULL-value where there is no image.

I am trying to use Inner Joins and LEFT OUTER JOIN but I want work, so please help me. This is how the question is build up:

Select pub.ID, pub.publishingStart, pub.PublishingStop, pub.UnitID, Texts.Title, Texts.Description, Texts.Script, images.ID, images.suffix, images.Height,images.width, images.script, pub.obj_type, pub.PriorityID, pub.CreatedDate, pub.UpdatedDate, Style.IAlign As ImgStyle
From Published as pub Inner Join Texts as texts
ON pub.UnitID = texts.UnitID LEFT OUTER JOIN Images as images
ON pub.UnitID = images.UnitID Inner Join Rel_StylePublished as relstyle
ON pub.ID = relstyle.publishedID Inner Join Styles As Style
On relstyle.StyleId = Style.ID
Where Pub.UnitId = 83 AND pub.PublishingStart < DateAdd(dd,-12,GETDATE()) AND
DateAdd(dd, 1, pub.PublishingStart) > DateAdd(dd,-12,GETDATE()) AND
(pub.PublishingStop > GETDATE() OR pub.PublishingStop IS NULL)
ORDER BY pub.PriorityId DESC, pub.PublishingStart Desc

Thanks!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-02-05 : 06:38:14

what i'm contributing isn't to do with your main problem....but more a pointer to a genral issue with your query.

see the code section below.

< DateAdd(dd,-12,GETDATE()) AND
DateAdd(dd, 1, pub.PublishingStart) > DateAdd(dd,-12,GETDATE()) AND
(pub.PublishingStop > GETDATE()

you would be best advised to assign the GETDATE value into a variable before executing the "SELECT" statement....and likewise convert the "dateadd"'s into variables where possible.

I've run into problems before with using the function GETDATE() in queries with inconsistent results (in complicated queries)...but also if you are (for instance) selecting 10000 rows...what's the point in performing the same calculation 10000 times, when you could do it once outside the query and use the result inside the query.

i would tink that something like the code snippent below would work more efficiently.

< @todaydateminus12days AND
DateAdd(dd, 1, pub.PublishingStart) > @todaydateminus12days AND
(pub.PublishingStop > @todaydate

where @todaydateminus12days and @todaydate are pre-calculated.

in fact "DateAdd(dd, 1, pub.PublishingStart) > @todaydateminus12days" might work out better (more efficient) if re-worked to be
"pub.PublishingStart > @todaydateminus11(or13)days"

as regards your main problem....I'll have to watch to see what my more learned colleagues post, so that I can learn from them!....from a quick glance I can't see what's wrong with your code.

Nazim
A custom title

1408 Posts

Posted - 2002-02-05 : 07:31:44

Apart from incorporating the recommendations mentioned by Arnold , if i were you i would have played with the type of joins in the query.

Try this

Select pub.ID, pub.publishingStart, pub.PublishingStop, pub.UnitID, Texts.Title, Texts.Description, Texts.Script, images.ID, images.suffix, images.Height,images.width, images.script, pub.obj_type, pub.PriorityID, pub.CreatedDate, pub.UpdatedDate, Style.IAlign As ImgStyle
From Published as pub Inner Join Texts as texts
ON pub.UnitID = texts.UnitID LEFT OUTER JOIN Images as images
ON pub.UnitID = images.UnitID left outer Join Rel_StylePublished as relstyle
ON pub.ID = relstyle.publishedID left outer Join Styles As Style
On relstyle.StyleId = Style.ID
Where Pub.UnitId = 83 AND pub.PublishingStart < DateAdd(dd,-12,GETDATE()) AND
DateAdd(dd, 1, pub.PublishingStart) > DateAdd(dd,-12,GETDATE()) AND
(pub.PublishingStop > GETDATE() OR pub.PublishingStop IS NULL)
ORDER BY pub.PriorityId DESC, pub.PublishingStart Desc

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

hesta96
Starting Member

9 Posts

Posted - 2002-02-05 : 08:47:25

The datething is just for test
Thanks for your help, I did succesed by myself and it ended up like this:

Select pub.ID, pub.publishingStart, pub.PublishingStop, pub.UnitID, Texts.Title, Texts.Description, Texts.Script, str(images.ID) + '.' + images.suffix as imageFile, images.Height as imageHeight,images.width as ImageWidth, images.script as imageDescription,pub.obj_type, pub.PriorityID, pub.UpdatedDate, Style.IAlign As imageStyle
From Published as pub Inner Join Rel_TextsPublished as Reltexts
ON pub.ID = relTexts.PublishedID Inner Join Texts as Texts
ON relTexts.TextID = Texts.ID LEFT OUTER JOIN Rel_ImagesPublished as relimages
ON relTexts.PublishedID = relimages.PublishedID LEFT OUTER JOIN Images as images
ON relimages.ImageID = images.ID LEFT OUTER JOIN Rel_StylePublished as relstyle
ON pub.ID = relstyle.publishedID LEFT OUTER JOIN Styles As Style
On relstyle.StyleId = Style.ID
Where Pub.Obj_Type = 1 AND pub.Active = 1 AND
Pub.UnitId = @UnitId AND pub.PublishingStart < DateAdd(dd,-12,GETDATE()) AND
DateAdd(dd, 1, pub.PublishingStart) > DateAdd(dd,-12,GETDATE()) AND
(pub.PublishingStop > GETDATE() OR pub.PublishingStop IS NULL)
ORDER BY pub.PriorityId DESC, pub.PublishingStart Desc

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources