I was scratching my head for about two hours the other day trying to figure out why a query wouldn't work. The query involves a left outer join and a simple inner join. I kept getting Oracle error ORA-00904: "ME"."ENTRY_ID": invalid identifier. Here's the query:

SELECT me.*, sc.*
FROM   mddba.md_entry me, mddba.de_spatial_coverage sc
LEFT OUTER JOIN aadc.metadata_datastores mdds ON me.entry_id = mdds.entry_id
WHERE  me.id = sc.dif_id

Strangely enough, to get the query working, all I had to do was swap the positions of "mddba.md_entry me" and "mddba.de_spatial_coverage sc". Seems if you are using both explicit and implicit join notation in a query, you have to make sure you declare the table you are joining to last. This is how it looks, working:

SELECT me.*, sc.*
FROM   mddba.de_spatial_coverage sc, mddba.md_entry me
LEFT OUTER JOIN aadc.metadata_datastores mdds ON me.entry_id = mdds.entry_id
WHERE  me.id = sc.dif_id

Of course, I could have just done this instead, using explicit notation only:

SELECT me.*, sc.*
FROM   mddba.md_entry me
INNER JOIN mddba.de_spatial_coverage sc on  sc.dif_id = me.id
LEFT OUTER JOIN aadc.metadata_datastores mdds 	ON me.entry_id = mdds.entry_id

I'm curious to see how other databases behave.