Jim Jim - 1 month ago 9
SQL Question

ms Access sql expand

I'd be grateful for some help with the following problem I'm having.
I have the following two tables (tblA and tblB):

tblA
+----+------+
| ID | item |
+----+------+
| 1 | c |
| 2 | a |
| 3 | b |
| 4 | e |
| 5 | d |
| 6 | f |
| 7 | a |
| 8 | c |
+----+------+


and

tblB
+----+----+
| f1 | f2 |
+----+----+
| a | |
| b | a |
| c | a |
| d | |
| e | |
| f | d |
| g | d |
| h | d |
| I | |
+----+----+


What I'd like to achieve is: where a value in the item field from tblA appears in the f2 field in tblB I'd like to select all tblB f1 values, together with the ID value from tblA, so that we get the following results:

+----+------+
| ID | item |
+----+------+
| 2 | b |
| 2 | c |
| 5 | f |
| 5 | g |
| 5 | h |
| 7 | b |
| 7 | c |
+----+------+


Note that ID values 2 and 7 from tblA, while having the same item value ('a') still appear separately in the results; this is required.

I've been playing about with the following ideas:

SELECT f1 FROM tblB
WHERE f2 IN(SELECT item FROM tblA)


//

SELECT ID,f1 FROM tblA,tblB
WHERE f2 IN(SELECT item FROM tblA)


//

SELECT x.ID, y.f1
FROM tblA AS x, tblB AS y
WHERE y.f2 IN(SELECT item FROM tblA)


But I'm struggling to get there.

Answer

I think this is just a join:

select a.id, b.f1 
from tblA as a inner join
     tblB as b
     on b.f2 = a.item
order by a.id;
Comments