Compare 2 values of different types inside of subquery

Question

I am using a MS SQL db and I have 3 tables: 'base_info', 'messages', 'config'

bases:
ID    Name   NameNum
====================================
 1    Home    101
 2    Castle  102
 3    Car     103

messages:
ID    Signal    RecBy    HQ
============================
111    120      Home     1
111    110      Castle   1
111    125      Car      1
222    120      Home     2
222    125      Castle   2
222    130      Car      2
333    100      Home     1
333    110      Car      2

config:
ID  SignalRec  SignalOut  RecBy   HQ
====================================
111     60        45       101    1
111     40        60       102    1
222     50        60       102    2
222     30        90       101    2
333     80        10       103    1

Ok so now I have a subquery in which I select the 'SignalRec' and 'SignalOut' from the config table and match it on the messages table by ID and Date(not included above), the problem is that I need it to match where messages.RecBy = config.RecBy but config.RecBy is a string but it's equivalent Name is in the bases table. So I almost need to do a subquery inside a subquery or some type of join and compare the returned value.
Here is what I have so far:

(SELECT TOP 1 config.SignalRec from config WHERE config.ID = messages.ID AND ||I need th other comparison here||...Order By...) As cfgSignalRec,
(SELECT TOP 1 config.SignalOut from config WHERE config.ID = messages.ID AND ||I need th other comparison here||...Order By...) As cfgSignalOut

I tried to make this as clear as possible but if you need more info let me know.

Answer 1

I would normalize out RecBy in your messages table to reference the bases table. Why would you insert the string content there if it's also referenced in bases?

This is exactly why normalization exists: reduce redundancy, reduce ambiguity, and enforce referential integrity.

To make this more clear, RecBy in the messages table should be a foreign key to Bases.

Answer 2

I think this could do the trick (although I have not tried it...)

SELECT 
    c.SignalRec 
FROM config c 
    INNER JOIN bases b 
        ON c.RecBy = b.NameNum 
    INNER JOIN messages m 
        ON b.Name = m.RecBy
WHERE c.ID = m.ID

However, as Anthony pointed out, you probably want to normalize out the strings in the RecBy column in the messages table, as you have the same data in the bases table.

Answer 3

From your description, it just sounds like you need two JOINS

SELECT TOP 1
    c.SignalRec
FROM 
    config c
INNER JOIN 
    bases b
ON c.RecBy = b.NameNum
INNER JOIN
    messages m
ON b.Name = m.RecBy

Answer 4

I think I might have not been clear enough what I wanted to do, sorry about that. The data is actually different in the 2 tablesthe correlations are the same. It's kin of confusing to explain without going into detail about how the system works.
I actually found a very fast way of doing this.
Inside my subquery I do this:

(SELECT TOP 1 config.Signal FROM config,bases WHERE config.ID = messages.ID AND bases.Name = messages.RecBy AND bases.NameNum = config.RecBy Order By...)

So this essentially compares the 2 RecBy's of different tables even though one is an integer and the other is a string. It reminds me of a match and lookup in Excel.