joining views with previous left joins and not multiplying rows

I have 2 views of the form:

--View1
SELECT foo.id AS id FROM foo LEFT JOIN bar ON foo.id = bar.id
--Results
id
 1
 1
 1
 2
 2
 ...


--View2
SELECT foo.id AS id FROM foo LEFT JOIN manchu ON foo.id = manchu.id
--Results
id
 1
 1
 1
 2
 2
 ...

Now I want to join the two views so that row #1 from View1 is joined to row #1 of View2.

If I join on View1.id = View2.id, then the rows will multiply by each other, which isn't what I want. Is there a way I can add a co开发者_JAVA百科lumn to each view with a unique number that I can join on? Or another solution?

Thanks in advance.

You can add the following as a column in each view:

ROW_NUMBER() over (order by id) as sequence

Your view join would then be View1.sequence = View2.sequence

But, your intent is still unclear. It appears you want to join row 1 in each view, and join row 2 in each view, etc. With what you've shown us, you're queries don't guarantee the row sequence for duplicate id values.

Use SELECT DISTINCT id in both views instead of SELECT id and join them on View1.id = View2.id. In this case you will have
id
1
2
etc.
If you need different results, plz specify in your question what exactly you want to get.