How to join 2 tables with multiple conditions each?-CodePudding

I have data split between 2 tables, and need to join the necessary data together for analysis. One table Test 3 Output contains ID numbers, and the return value of the test. The other table Test Results contains the same IDs, along with their corresponding serial number and overall test result.

I need to combine these into a single table that just displays ID, serial number and test value.

Sorry in advance for the horrible SQL thats about to follow, I'm brand new to this.

I have 2 working queries that give me what I want, but I can't seem to join them together.

The first query:

select `ID`,`Serial Number` from `Test Results`t where (len(`Serial Number`)=16 and FailMode = '24V Supply FAIL')

This gets me the ID and serial number of all the tests that failed '24V supply'. It also filters out garbage serial numbers as the correct ones should have 16 digits.

The second query:

select `ID` from `Test 3 Output`o where o.`24V Supply (V)`<30

This gets me the ID and test results, and filters out some results that were greater than 30V. Note that '24V Supply(V) is the name of the column containing the test results.

Now when I try to join these with the ID, I get a syntax error. Here's what I tried:

select `ID`,`Serial Number` 
from `Test Results`t 
where (len(`Serial Number`)=16 and FailMode = '24V Supply FAIL') 
left join (`Test 3 Output`o ON t.`ID` = o.`ID`  where o.`24V Supply (V)`<30)

This gives the error:

Error: Syntax error (missing operator) in query expression (len(`Serial Number`)=16 and FailMode = '24V Supply FAIL') left join (`Test 3 Output`o ON t.`ID` = o.`ID`  where o.`24V Supply (V)`<30)

I'm not sure what operator I'm missing but I had a feeling its related to the fact there's two where statements? Can anyone offer some help?

Edit: I found a workaround since I can't use 2 where clauses with a join. I created 2 views with my 2 separate queries, and performed the join on those which got me what I wanted. I'd still like to hear a proper way of doing it though :)

CodePudding user response：

You can join 2 subqueries like this:

SELECT q1.a, q1.b, q2.c
FROM (
  (SELECT a, b FROM table1
   WHERE b > 10) AS q1
  LEFT JOIN
  (SELECT a, c FROM table2
   WHERE c > 20) AS q2
  ON q1.a = q2.a
)

Doing the subqueries as separate query objects is easier to debug, but the query objects keep piling up...