SQL - Joining multiple selects/tables

Question

My first query...

SELECT
    t1.a, t1.b, t1.c,
    t2.a, t2.b, t2.c

FROM t1

LEFT JOIN t2 ON t1.a = t2.a

WHERE t1.b = '000000'

AND LENGTH(t1.a) > '5'

AND t1.c <> 'Y';

My second query...

SELECT
    t1.a,
    t3.b as testMe

FROM t1

LEFT JOIN t3 ON t1.a = '0' + LEFT(t3.a, 5)

WHERE t1.a = '017941';

Both of these queries work fine by themselves, but I need them combined into one result set. Worth noting is that the where clause in the second query is there for testing purposes, but when I remove it the whole thing crashes. Not sure if that means I need something to filter by, or it's timing out? The database I'm using is Pervasive.

My failed query...

SELECT
    t1.a, t1.b, t1.c,
    t2.a, t2.b, t2.c,
    t3.b as testMe

FROM t1

LEFT JOIN t2 ON t1.a = t2.a

LEFT JOIN t3 ON t1.a = '0' + LEFT(t3.a, 5)

WHERE t1.b = '000000'

AND LENGTH(t1.a) > '5'

AND t1.c <> 'Y';

I've shorted my code to better show what I have going on, but the actual code can be found here: http://codeshare.io/A2aB9

please be more specific - what is your RDBMS, what is "it" that is crashing? — Kritner, Oct 01 '15 at 15:11
I've updated my original post to better show what I have going on. — Waxi, Oct 01 '15 at 17:21

score 0 · Answer 1 · answered Oct 01 '15 at 18:51

You can try to use those two sets as subselect statements and join them together. I pulled your join condition between the two queries out as a separate column in order to keep testMe in the result set.

SELECT  *
FROM    (SELECT t1.a t1_a
              , t1.b t1_b
              , t1.c t1_c
              , t2.a t2_a
              , t2.b t2_b
              , t2.c t2_c
         FROM   t1
         LEFT JOIN t2
                ON t1.a = t2.a
         WHERE  t1.b = '000000'
                AND LENGTH(t1.a) > '5'
                AND t1.c <> 'Y') a
LEFT JOIN (SELECT   t1.a t1_a
                  , t1.b t1_b
                  , t1.c t1_c
                  , t3.a t3_a
                  , t3.b t3_b
                  , '0' + LEFT(t3.c, 5) t3_c
                  , LEFT(t3.c, 5) AS testMe
           FROM     t1
           LEFT JOIN t3
                    ON t1.a = '0' + LEFT(t3.c, 5)) b
        ON a.t1_a = c.t3_c;

I tried doing this and it didn't do anything. Thank you though. — Waxi, Oct 01 '15 at 20:27
Can you be more specific? Did it not return any rows? Did it return the wrong rows? — Vinnie, Oct 01 '15 at 20:28
http://codeshare.io/A2aB9 shows my actual code. At the very bottom of your line you have a `c.t3_c` which I think was supposed to be a `b.t3_c`, but either way, I get a syntax error and it shows `ON a.job_header.<< ???`. — Waxi, Oct 01 '15 at 20:49

SQL - Joining multiple selects/tables

1 Answers1