JOIN outside of WHERE clause ? ? ? ?

view 1

I have a view that is drawing from two tables. Each table contains fields representing cube coordinates. The view is filtering the results based on some simple logic (where the defference between two values in the tables are greater than x) this part works fine.

view 2

notes field

I want to include a note field in my view. This field will contain the contents of a note field from another view. This second view also contains coordinates that I can use to map the notes to the appropriate rows in view 1. However, if I join the views in my FROM clause, I will end up filtering my resultset down to rows that correspond to view 2's contents.

I want to have the full contents of view 1, displayed with a note field containing the note field content from view 2 only in the rows that have corresponding notes. (some rows will have notes, some will not)

eg.

VIEW 1

row1 row2 row3 note_row (from view 2)

fsdfs sdfsdf sdfsdf <no note>

sdfs sdfsd sdfsd "note"

sdfsdf sdfsdf ssdfsd <no note>

so... my question: is there any way that I can include this field without joining the views in my FROM clause (meking my resultset exclusive)..... possibly somehow in fields list of the select statement?

THANKS!

Sounds like a UNION or UNION ALL would do the trick because you could avoid joining the two recordsets. Create that as your inner query and query it as a virtual table, maybe?

Lee Everest

www.texastoo.com/sqlblog

|||

You're looking at left join.

e.g.

Code Snippet

select v1.*, v2.notes

from v1 left join v2 on v1.id=v2.id

|||Thanks. I realized this in the middle of the night last night and tried it out... the LEFT JOIN works. Thanks.