Replacing Where with 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 2000 Forums

SQL Server Development (2000)

Replacing Where with Join

Author

Topic

cfdev
Starting Member

1 Post

Posted - 2007-02-22 : 20:00:54

SELECT DISTINCT Image.ImageID, Image.JobID, Image.Filename, Tag.Name,
Tag.SortOrder, TagCat.name

FROM Image, JobTag, Tag, Job, Tagcat

WHERE Tag.TagID = JobTag.TagID
AND Image.JobID = Job.JobID AND Job.JobID = JobTag.JobID
AND TagCat.TagCatID = Tag.TagCatID

ORDER BY Image.ImageID, Tag.SortOrder

I'd like to replace the query above with a some kind of a join, but I'm not sure how to do it. The reason I want to do this is to only get the category (TagCat.name) one time for each set of tags that go under that category for that particular job. Any thoughts on how I could do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-22 : 20:16:26

Switching this to a join isn't going to change the outcome of the query. What you need is a GROUP BY. Please show us a few rows that the query is currently returning then show us what you want it to return.

Tara Kizer

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-22 : 20:16:32

[code]
SELECT DISTINCT Image.ImageID, Image.JobID, Image.Filename, Tag.Name,
Tag.SortOrder, TagCat.name
FROM Image
INNER JOIN Job ON Image.JobID = Job.JobID
INNER JOIN JobTag ON Job.JobID = JobTag.JobID
INNER JOIN Tag ON Tag.TagID = JobTag.TagID
INNER JOIN Tagcat ON TagCat.TagCatID = Tag.TagCatID
ORDER BY Image.ImageID, Tag.SortOrder
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources