Union query (I thinK)

[msnews]

Access 2000, trying to filter one table based on the contents of another.
Table A
A.ID =1234
A.FLAG = WHATEVER

Table B
B.ID =1234

I have the following select statement:
Set rs1 = db1.OpenRecordset("select B.ID, B.ListID, B.PartID, B.Qty from B
where " _
& "B.ListID=" & strParentID)

Multiple records will be returned from B and I'd like to further filter them
using Table A -- only return records from B if the field A.FLAG = "some
value"

TIA

 

[Jason Lepack]

I've determined based on your description that there will be a one to
one relationship between A.ID and B.ID. This makes me wonder why
A.FLAG was not instead stored in B. But the SQL for your current
question is:

SELECT B.*
FROM B INNER JOIN A ON A.ID = B.ID
WHERE A.FLAG = "Some Value"

Cheers,
Jason Lepack

 

[msnews]

Thanks for the info Jason. Sorry for the lack of details.

The relationship is One (A) to Many (B).
My apologies - I have a typo in my request:
Table A
A.PartID =1234
A.FLAG = WHATEVER

Table B
B.PartID =1234

Also note that I am already filtering B on B.ListID. Since the Where clause
you provided only shows the A.FLAG I don't think it will work for me. I'll
read up a bit on INNER join though...

Thanks

 

[Jason Lepack]

This should do it then:

SELECT B.ID, B.ListID, B.PartID, B.Qty
FROM B INNER JOIN A ON B.PartID = A.PartID
WHERE A.FLAG = 'Some Value' AND B.ListID = yourID

Cheers,
Jason Lepack

 

[msnews]

Jason,

Thanks for the info, it was very helpful. Turns out I was asking the wrong
question, though! I didn't need to filter the results, I just needed access
to FLAG in the second db so I could make an appropriate decision later on.
The following is what I ended up using.

"SELECT B.ID, B.ListID, B.PartID, B.Qty, A.FLAG from A, B " _
& "WHERE A.ID = B.PartID AND " _
& "B.ListID=" & strParentID

Your help is much appreciated!