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