Filter on Joined Table [RESOLVED]

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)

Filter on Joined Table [RESOLVED]

Author

Topic

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2007-12-07 : 06:19:58

Hey SQL Team!

I have the following;

USE HSS
SELECT
Job.[Job No],
Item.[Code],
CONVERT (CHAR(10), Job.[Date Rec'd], 103) AS DateRecieved,
CONVERT (CHAR(10), Job.[ProdStartActual], 103) AS ProductionStart,
CONVERT (CHAR(10), Job.[Compl Date], 103) AS CompletedDate,
Job.[Assembler]
FROM
Job INNER JOIN
Item
ON Job.[Job No]=Item.[Job No] INNER JOIN
BOMData
ON Item.[Code]=BOMData.[Component]
WHERE
Job.[Assembler]='GOH' OR
Job.[Assembler]='GH' OR
Job.[Assembler]='GO' ;
GO

Which is also returning Items with code 'CA', I cant remember how to remove these... Do I have to do a select on a derived table. Funny how the simple ones like this seem to get me about 60% of the time.

Thanks for your help.
pace_

"Impossible is Nothing"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-07 : 06:37:40

[code]SELECT j.[Job No],
i.[Code],
CONVERT(CHAR(10), j.[Date Rec'd], 103) AS DateRecieved,
CONVERT(CHAR(10), j.[ProdStartActual], 103) AS ProductionStart,
CONVERT(CHAR(10), j.[Compl Date], 103) AS CompletedDate,
j.[Assembler]
FROM Job AS j
INNER JOIN Item AS i ON i.[Job No] = j.[Job No]
AND i.[Code] <> 'CA'
INNER JOIN BOMData AS bd ON bd.[Component] = i.[Code]
WHERE j.[Assembler] IN ('GOH', 'GH', 'GO')[/code]Or[code]SELECT j.[Job No],
i.[Code],
CONVERT(CHAR(10), j.[Date Rec'd], 103) AS DateRecieved,
CONVERT(CHAR(10), j.[ProdStartActual], 103) AS ProductionStart,
CONVERT(CHAR(10), j.[Compl Date], 103) AS CompletedDate,
j.[Assembler]
FROM Job AS j
INNER JOIN Item AS i ON i.[Job No] = j.[Job No]
INNER JOIN BOMData AS bd ON bd.[Component] = i.[Code]
WHERE j.[Assembler] IN ('GOH', 'GH', 'GO')
AND i.[Code] <> 'CA'[/code]

E 12°55'05.25"
N 56°04'39.16"

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2007-12-07 : 06:51:02

As always worked wonderfully thanks Peso.

Plus, I learnt that you could add criteria to a join. "Item AS i ON i.[Job No] = j.[Job No] AND i.[Code] <> 'CA'" My new trick for today, wasnt aware of that one before :-) Thanks again.

"Impossible is Nothing"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-07 : 06:59:36

It is not always the better choice to put the criteria on the JOIN.
Sometimes it will give a worse query plan. Check both alternatives to see which gives better performance for your environment.

Thank you for your feedback.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources