Complex Query or just me?

i have 2 tables. i want to compare 2 columns from the first table and display the contents in the second table (hard to explain)
example:

[TableA]
id ans1 ans2
=============================
bob abc xyz
joe abc www
mike def www
foo def xyz
bar abc xyz

[TableB]
anscode anstext
=========================================
abc Abc is the first 3 letters
def DEF JAM music
www World Wide Web
xyz XYZ best 3 looking letters in the alphabet

[Result should be]
Column1 Column2 Count
================================================== ========================================
Abc is the first 3 letters XYZ best 3 looking letters in the alphabet 2
Abc is the first 3 letters World Wide Web 1
DEF JAM music World Wide We 1
DEF JAM music XYZ best 3 looking letters in the alphabet 1select distinct
b1.anstext, b2.anstext
from tablea a, tableb b1, tableb b2
where a.ans1 = b1.anscode
and a.ans2 = b2.anscode;|||thnx for the reply
im going to try it out when i get home.

leaving work now

thnx|||actually, instead of DISTINCT, this requires a GROUP BY, because the COUNT is needed

so to modify littlefoot's code slightly...select b1.anstext
, b2.anstext
, count(*) as occurrences
from tablea a
inner
join tableb b1
on a.ans1 = b1.anscode
inner
join tableb b2
on a.ans2 = b2.anscode
group
by b1.anstext
, b2.anstext|||perfect
thats what i needed
thnx

just curious, how much different would it be if i wanted to show every possibly, meaning the of the counts would be 0 if the match never occurs. if it is a major add-on dont worry - not sure what the client really wants. thnx|||Gosh, Rudy ... didn't scroll right enough to see the "count" output column ... Sorry, Vextout.

As of your last post: I'd say you'll need outer join to fetch such records.|||yes, just change INNER to LEFT OUTER in the query i gave you|||thnx again

i started playing with the final query i had that with the inner join trying to display all the possibilites with the counts and realized that the whole query has to be changed, but at least i know what to do now.

thnx again for all the help