joining views with previous left joins and not multiplying rows

Question

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 column to each view with a unique number that I can join on? Or another solution?

Thanks in advance.

Answer 1

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.

Answer 2

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.