Left Join to another select statement

Question

I have an INNER JOIN query that runs perfectly fine by itself, which I want to LEFT JOIN to a another query/SELECT statement which contains WHERE clause. I'm not able to join both the queries. It should link wt.tkinit = t.tkinit Can you please suggest what I'm missing.

SELECT c.clnum, m.mmatter, ot.tkinit AS 'otkinit', wt.tkinit AS 'wtikint', t.tkrt01,
SUM(mt.mthrwkdb) AS 'whrs2010',
FROM client c, matter m, timekeep ot, timekeep wt, mattimhs mt, periodt p, timerate t
WHERE c.clnum = m.mclient
AND m.mmatter = mt.mtmatter
GROUP BY c.clnum, m.mmatter, ot.tkinit, wt.tkinit

SELECT t.tkinit, t.tkrt01
FROM timerate t
INNER JOIN (
SELECT tkinit, max(tkeffdate) as max_effdate 
FROM timerate WHERE DATEPART(year, tkeffdate) = '2012'
GROUP BY tkinit) mt ON mt.tkinit = t.tkinit AND mt.max_effdate = t.tkeffdate

Toby Allen · Accepted Answer · 2013-07-22 18:48:23Z

1

Did you try the following:

SELECT fields from (SELECT1) s1 
  LEFT JOIN 
  (SELECT2) s2 
  on s1.wt.tkinit = s2.t.tkinit.

Don't forget to get normal name for wt.tkinit and t.tkinit in their selects like wt_tkinit and t_tkinit because you can't do double aliases.

edited Jul 22, 2013 at 18:48

Toby Allen

11.3k12 gold badges80 silver badges132 bronze badges

answered Jul 22, 2013 at 18:45

Andrej

7,5041 gold badge22 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Manu Over a year ago

sorry no luck. What is that s1 and s2 represent. temp table?

Andrej Over a year ago

This is your two queries. Put it instead of SELECT1 and SELECT2

tkendrick20 · Accepted Answer · 2013-07-22 18:55:43Z

0

SELECT temp2.tkinit, temp2.tkrt01, temp.clnum, temp.mmatter, temp.otkinit, temp.tkrt01, temp.whrs2010
FROM
    ( SELECT c.clnum, m.mmatter, ot.tkinit AS 'otkinit', wt.tkinit AS 'wtikint', t.tkrt01,
    SUM(mt.mthrwkdb) AS 'whrs2010',
    FROM client c, matter m, timekeep ot, timekeep wt, mattimhs mt, periodt p, timerate t
    WHERE c.clnum = m.mclient
    AND m.mmatter = mt.mtmatter
    GROUP BY c.clnum, m.mmatter, ot.tkinit, wt.tkinit ) temp
LEFT JOIN 
    SELECT t.tkinit, t.tkrt01
    FROM timerate t
    INNER JOIN (
    SELECT tkinit, max(tkeffdate) as max_effdate 
    FROM timerate WHERE DATEPART(year, tkeffdate) = '2012'
    GROUP BY tkinit) mt ON mt.tkinit = t.tkinit AND mt.max_effdate = t.tkeffdate ) temp2
ON temp.wtikint = temp2.tkinit

Be careful of your field names, but this may be a start

answered Jul 22, 2013 at 18:55

tkendrick20

5201 gold badge4 silver badges14 bronze badges

2 Comments

Manu Over a year ago

I'm getting this erroe message

tkendrick20 Over a year ago

Check your field names

Collectives™ on Stack Overflow

Left Join to another select statement

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related