New Problem, small change hangs Access/TOAD

[SquallStrife]

OK, sorted out that last problem I had.... Heres a new one.

This query runs fine:

SELECT [Piping Models by Area].AREA_NAME, [Piping Models by Area].MODEL_NO, [All Pipes].LINE_ID, [All Pipes].PIPING_MATER_CLASS, [All Pipes].PIPE_LENGTH

FROM [Piping Models by Area] LEFT JOIN [All Pipes] ON [Piping Models by Area].PARTITION_NO = [All Pipes].PARTITION_NO

WHERE [Piping Models by Area].AREA_NAME like "545*"

ORDER BY [Piping Models by Area].AREA_NAME, [Piping Models by Area].MODEL_NO, [All Pipes].LINE_ID, [All Pipes].PIPING_MATER_CLASS;

Add one WHERE criteria, and it no longer runs, under Access or TOAD for Oracle, or SQL*Plus, or anything:

SELECT [Piping Models by Area].AREA_NAME, [Piping Models by Area].MODEL_NO, [All Pipes].LINE_ID, [All Pipes].PIPING_MATER_CLASS, [All Pipes].PIPE_LENGTH

FROM [Piping Models by Area] LEFT JOIN [All Pipes] ON [Piping Models by Area].PARTITION_NO = [All Pipes].PARTITION_NO

WHERE [Piping Models by Area].AREA_NAME like "545*" and [All Pipes].PIPING_MATER_CLASS = "F13B"

ORDER BY [Piping Models by Area].AREA_NAME, [Piping Models by Area].MODEL_NO, [All Pipes].LINE_ID, [All Pipes].PIPING_MATER_CLASS;

It works fine, and is nice and fast, until I add the second "WHERE" criteria.

[Piping Models by Area] and [All Pipes] are both queries and run fine on their own. This query also runs fine without the WHERE clause.

Any ideas? Is there some small syntactical error causing some kind of infinite loop or something?

Edit: formatted for easier viewing

 

[namliam]

SELECT [Piping Models by Area].AREA_NAME, [Piping Models by Area].MODEL_NO, [All Pipes].LINE_ID, [All Pipes].PIPING_MATER_CLASS, [All Pipes].PIPE_LENGTH

FROM [Piping Models by Area] LEFT JOIN [All Pipes] ON [Piping Models by Area].PARTITION_NO = [All Pipes].PARTITION_NO

WHERE [Piping Models by Area].AREA_NAME like "545*" and [All Pipes].PIPING_MATER_CLASS = "F13B"

ORDER BY [Piping Models by Area].AREA_NAME, [Piping Models by Area].MODEL_NO, [All Pipes].LINE_ID, [All Pipes].PIPING_MATER_CLASS;

In your second query as show above you are putting contraints on the right side of a left join... This translates into an innerjoin...

If you want/need a Left join you should do either of below:
1)
Change: and [All Pipes].PIPING_MATER_CLASS = "F13B"
to: and ([All Pipes].PIPING_MATER_CLASS = "F13B" or[All Pipes].PARTITION_NO is null)

2)
Change your from/where to:
FROM [Piping Models by Area] LEFT JOIN (select * from [All Pipes] where [All Pipes][/b].PIPING_MATER_CLASS = "F13B") "All Pipes" ON [Piping Models by Area].PARTITION_NO = [All Pipes].PARTITION_NO
WHERE [Piping Models by Area].AREA_NAME like "545*"

Note: Spaces in tables names are HEADACHES... In general it is considered "bad practice"

I hope this helps....