How do I Write a SQL Query With a Condition Involving a Second Table?

Question

Table1
...
LogEntryID *PrimaryKey*
Value
ThresholdID - - - Link to the appropriate threshold being applied to this log entry.
...

Table2
...
ThresholdID *PrimaryKey*
Threshold
...

All fields are integers.
The "..." thingies are there to show that these tables hold a lot more imformation than just this. They are set up this way for a reason, and I can't change it at this point.

I need write a SQL statement to select every record from Table1 where the Value field in that particular log record is less than the Threshold field in the linked record of Table2.

I'm newish to SQL, so I know this is a basic question.
If anyone can show me how this SQL statement would be structured, it would be greatly appreciated.

Answer 1

SELECT * FROM Table1
JOIN Table2
ON table1.ThresholdID = table2.ThresholdID --(assuming table 2 holds the same value to link them together)
WHERE
value < thresholdvalue

A 'JOIN' connects 2 tables based on the 'ON' clause (which can be multipart, using 'AND' and 'OR')

If you have 3 entries in table 2 which share table1's primary key (a one-to-many association) you will receive 3 rows in your result set.

for the tables below, for example:

Table 1:
Key     Value
1       Hi
2       Bye

Table 2:
Table1Key  2nd_word
1           You
1           fellow
1           friend
2           now

this query:

SELECT * FROM Table1 JOIN Table2 on table1.key = table2.table1key

gets this result set:

Key    Value    Table1Key   2nd_word
1      Hi        1          You
1      Hi        1          fellow
1      Hi        1          friend
2      Bye       2          now

Note that JOIN will only return results when there is a match in the 2nd table, it will not return a result if there is no match. You can LEFT JOIN for that (all fields from the second table will be NULL).

JOINs can also be strung together, the result from the previous JOIN is used in place of the original table.

Answer 2

SELECT T1.*
  FROM Table1 T1
  JOIN Table2 T2 ON T2.ThresholdID = T1.ThresholdID
 WHERE T2.Threshold > T1.Value

Answer 3

SELECT t1.*
FROM dbo.Table1 t1 INNER JOIN dbo.Table2 t2 ON t1.ThresholdID = t2.ThresholdID
WHERE t2.Threshold > t1.Value

Answer 4

SELECT * from table1 t1 join table2 t2 on (t1.thresholdId = t2.thresholdId) where t1.value < t2.threshold;

Answer 5

SELECT t1.LogEntryID, t1.Value, t1.ThresholdID
FROM Table1 t1 
INNER JOIN Table2 t2 ON t1.ThresholdID = t2.ThresholdID 
WHERE t1.Value < t2.threshold