Web Development



Join a query with one of the Tables used to generate the query

By rayasta ·
Hi to everybody,

I would like to know if it's possible to do this in MSAccess SQL:

Select *
FROM (table1 LEFT JOIN table2 ON table1.field1 = table2.field1) LEFT JOIN table2 ON table1.field2 = table2.field1

Note that table2 is being used twice, on each LEFT JOIN.

When I try this on MSAccess SQL view, it gives me an error. Is there any other way to do it? I did it using two separate queries, but I would like to know if it's possible to do it on one.

Thanks in advance for the help.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Try this

by LocoLobo In reply to Join a query with one of ...

Use aliases for table2, it works for self joins.

Select *
FROM (table1 LEFT JOIN table2 as T2A
ON table1.field1 = T2A.field1) LEFT JOIN
table2 AS T2B ON table1.field2 = T2B.field1

I don't understand your query. You don't seem to be selecting data from both tables nor filtering the data from table1. I could be wrong, but it looks like even if the query works you will get the same results as

FROM table1

What are you trying to do with the query?

Collapse -

Brief Explanation

by rayasta In reply to Try this

I want to select all records on Table1 where Table1.field1 or Table1.field2 DO NOT EXIST on Table2.field1.

I didn't add the filtering or selecting on my first post for ease to read, but I made it more complex... Sorry about that.

I tried the aliases and it worked! Thanks!

Collapse -


by LocoLobo In reply to Brief Explanation

glad it worked. I knew it worked with self joins but your case looked just a little different.

Collapse -


by gsquared In reply to Brief Explanation

Try this:

select *
from table1
where field1 not in
(select field1
from table2)
and field2 not in
(select field1
from table2)

That will get what you say you're looking for here.

Collapse -

How about

by Tony Hopkinson In reply to Solution

Select * From Table1 TM1
Where TM1.Table1Key Not In
(Select Table1Key
From Table1 T1, Table2 T2a, Table2 T2b
Where T1.Field1 = T2a.Field1
and T1.Field2 = T2b.Field1)

Assuming of course table1 has a suitable primary key.

Collapse -

End Result Desired

by gsquared In reply to Join a query with one of ...

Are you trying to get all the columns from Table2 twice each?

Or are you trying to restrict the rows returned by Table1.field1 and Table1.field2?

If what you want is all the columns from Table2 twice, then yes, you can do something like that.

If what you want is columns from Table1 and Table2, with a double restriction on it, try this:

Select *
from Table1 left join Table2 on table1.field1 = table2.field1 and table1.field2 = table2.field1

The "and" clause in the join gives you two restrictions on the join. You could also use "or" if what you want is "Table2.field1 is equal to Table1.field1 or table1.field2".

I can't quite tell from your query what the desired end result is.

The error your getting is almost certainly from the parenthesis. When you use those in a From statement, you have to include a full subquery in them and give it a name:

select *
from (select table1.*
from table1 left outer join table2
on table1.field1 = table2.field2) as Name
left join table2
on name.field2 = table2.field1

"Name" in this case is a named sub-query which would have all the fields from table1 in it. (In MS SQL, you can ommit the "as" before the subquery name, so you can probably do the same in Access, but I'm not certain on that point.)

Please let me know if that helps. If you can clarify what you are trying to accomplish with the query, I can provide more specific advice.

Collapse -

Two times Table2 column

by rayasta In reply to End Result Desired

Yes, what I want is twice Table2 columns (not all of them). I gave a bit more explanation about what I wanted on a previous post.

I believe I got the error because I didn't use aliases on my first try. It seems there is a problem with MSAccess when you try to re-use the same table on two joins (considering the result of one join is used as input for the other one).

Related Discussions

Related Forums