CODEWITHSUNDEEP
mysql
Manu
Manu

Reputation: 11

Left Join to another select statement

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

Upvotes: 1

Views: 113

Answers (2)

tkendrick20
tkendrick20

Reputation: 520

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

Upvotes: 0

Andrej
Andrej

Reputation: 7504

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.

Upvotes: 1

Related Questions