Joining a third table, but not a typical join

This is my query right now:

 SELECT
 a.stacct, b.hacct, a.stfrqcode, b.thfrqcode 
 FROM st a
 LEFT JOIN th b
 ON b.hacct = a.stacct

The issue: I have a third table that has the relation of e开发者_Python百科ach stfrqcode to each thfrqcode (for example ax = d4); problem is how do I pull back everything and tell if they match? In the reference table there are two columns stfrqcode and thfrqcode -- they have different values in each column.

Schema

table st:

stacct,

stfrqcode

table th:

hacct,

thfrqcode

table frqcodes:

thfrqcode,

stfrqcode

data from frqcodes

ns x00

ed x22

zs x33

ao x44

Result set should be: stacct, hacct, and basically a yes/no if there is a match between stfrqcode and thfrqcode.

SELECT
   a.stacct
  ,b.hacct
  ,case when c.stfrqcode is null then 'No' else 'Yes' end IsMatch
 from st a
  left outer join th b
   on b.hacct = a.stacct
  left outer join ThirdTable c
   on c.stfrqcode = a.stfrqcode
    and c.thfrqcode = b.thfrqcode

...might be some typos in there, I couldn't debug it.

Assuming I understand your table structure (with some dummy values I created):

Table st

stacct    stfrqcode
fred         A
mary         B
joseph       C

Table th

hacct     thfrqcode
fred         J
mary         H

Table relation

stfrqcode   thfrqcode
   A            J
   B            Q

and you are wanting this solution (I removed hacct as the value can only be either equal to stacct or null if there is no match):

stacct    match
fred       yes
mary       no
joseph     no

this is the query I would try:

SELECT
 a.stacct, (CASE WHEN c.strfrqcode IS NULL THEN 'no' ELSE 'yes' END) AS match
 FROM st a
 LEFT JOIN th b
 ON b.hacct = a.stacct
 LEFT JOIN relation c
 ON c.strfrqcode = a.strfrqcode and c.thfrqcode = b.thrfrqcode and b.thrfrqcode IS NOT NULL

If your frqcodes table isn't necessarily unique on (stfrqcode, thfrqcode), or just because you can, you may like to try an exists clause in the select list:

SELECT a.stacct, b.hacct, a.stfrqcode, b.thfrqcode,
       CASE WHEN EXISTS (SELECT 1
                         FROM frqcodes AS c
                         WHERE c.stfrqcode = a.stfrqcode
                           AND c.thfrqcode = b.thfrqcode)
            THEN 'Yes'
            ELSE 'No'
       END AS relationship_exists
FROM st a
     LEFT JOIN th b
     ON b.hacct = a.stacct