query help

[igg via AccessMonster.com]

access 2003:
I need to get to tableC from tableA and TableB. I tried hard to get to TableC
but had no luck.

tableA:
ID N1 N2 N3
1 12 A1 B1
2 12 D4 Z1
3 45 V5 S3
4 45 F3 O2
5 10 G3 I2

TableB:
ID N1 Y1
20 12 33
21 55 51
22 67 23
23 10 34

result:
TableC:
ID Y1 N1 N2 N3
1 33 12 A1 B1
2 33 12 D4 Z1
3 45 V5 S3
4 45 F3 O2
5 34 10 G3 I2

so on.

 

[hennie]

One thing i do notice is their is no relation between the two tables. You
cannot link the two tables to obtain the result you need.

 

[John Spencer]

So does TableA.N1 match up against TableB.N1?

Assuming that is true
SELECT TableA.ID, TableB.Y1, TableA.N1, TableA.N2, TableA.N3
FROM TableA LEFT JOIN TableB
ON TableA.N1 = TableB.N1

If you are doing this in query design view
== Add both tables
== Drag from TableA.N1 to TableB.N1
== Double-click on the join line and select the option that gives you ALL
records in TableA and matching records in TableB
== Select the fields you want to display.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[igg via AccessMonster.com]

This query works but how to use where statement such as where not exists or
where exits instead of "Left join" to get the same result.

So does TableA.N1 match up against TableB.N1?

Assuming that is true
SELECT TableA.ID, TableB.Y1, TableA.N1, TableA.N2, TableA.N3
FROM TableA LEFT JOIN TableB
ON TableA.N1 = TableB.N1

If you are doing this in query design view
== Add both tables
== Drag from TableA.N1 to TableB.N1
== Double-click on the join line and select the option that gives you ALL
records in TableA and matching records in TableB
== Select the fields you want to display.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

access 2003:
I need to get to tableC from tableA and TableB. I tried hard to get to TableC

[quoted text clipped - 25 lines]

so on.

 

[John Spencer]

I don't know of a way to return the results you stated you wanted using an
exists or not exists subquery.

I guess you could use a subquery to return the one field in tableB

SELECT TableA.ID
, (Select Y1 FROM TableB WHERE TableB.N1 = TableA.N1) as Y1
, TableA.N1
, TableA.N2
, TableA.N3
FROM TableA

That query will be less efficient than using the LEFT JOIN since you are using
a correlated subquery to get the ONE field you want. That means that you will
run a query against TableB for every row in tableA.

If all you want to know is whether or not (true or False) a value exists in
TableB for Y1 then

SELECT TableA.ID
, Exists (Select Y1 FROM TableB WHERE TableB.N1 = TableA.N1) as Y1Exists
, TableA.N1
, TableA.N2
, TableA.N3
FROM TableA

If you want to know if a matching ROW exists (whether or not Y1 is Null or not)

SELECT TableA.ID
, Exists (Select * FROM TableB WHERE TableB.N1 = TableA.N1) as Y1Exists
, TableA.N1
, TableA.N2
, TableA.N3
FROM TableA


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

This query works but how to use where statement such as where not exists or
where exits instead of "Left join" to get the same result.

So does TableA.N1 match up against TableB.N1?

Assuming that is true
SELECT TableA.ID, TableB.Y1, TableA.N1, TableA.N2, TableA.N3
FROM TableA LEFT JOIN TableB
ON TableA.N1 = TableB.N1

If you are doing this in query design view
== Add both tables
== Drag from TableA.N1 to TableB.N1
== Double-click on the join line and select the option that gives you ALL
records in TableA and matching records in TableB
== Select the fields you want to display.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

access 2003:
I need to get to tableC from tableA and TableB. I tried hard to get to TableC

[quoted text clipped - 25 lines]

so on.