inner join, group by sytax errors

[emmy]

I'm sure the problem with this SQL goes deeper than syntax error. This worked great before I tried to add email addresses to my results.
Syntax error on line 13 and 23:

(SELECT
a.EMPLID,
a.EMPL_RCD,
a.EMPL_STATUS,
a.HR_STATUS,
a.EFFSEQ,
a.ACTION,
(CONVERT(CHAR(10),a.[EFFDT],110)) AS EFF_DATE,
c.[EMAIL_ADDR]
FROM
[JOB] a, [EMAIL_ADDRESSES] c
WHERE
a.[EMPLID] = c.[EMPLID]) d
INNER JOIN
(SELECT
[PS_JOB].[EMPLID],
MIN([PS_JOB].[EMPL_RCD]) AS MIN_EMPL_RCD,
MAX([PS_JOB].[EFFSEQ]) AS MAX_EFF_SEQ,
MAX([PS_JOB].[EFFDT]) AS MAX_EFF_DATE
FROM
[JOB]
GROUP BY
[JOB].[EMPLID]) b
ON
d.[EMPLID] = b.[EMPLID]
AND d.[EFFDT] = b.MAX_EFF_DATE
AND d.[EFFSEQ] = b.MAX_EFF_SEQ
AND d.[EMPL_RCD] = b.MIN_EMPL_RCD
AND d.[EMPL_STATUS] = 'A'
AND d.[HR_STATUS] = 'A'

 

[AlexCuse]

FROM 
[JOB] a, [EMAIL_ADDRESSES] c
WHERE
a.[EMPLID] = c.[EMPLID]) d

You should forget you ever knew how to write joins this way. Here is how you should have done it with those nonsense joins intact. Notice that the join condition for your first join is WAAAAAY down at the bottom. This is especially bad because you are using both types of joins. I would hate to have to debug this query.

SELECT 
a.EMPLID, 
a.EMPL_RCD, 
a.EMPL_STATUS, 
a.HR_STATUS, 
a.EFFSEQ, 
a.ACTION, 
(CONVERT(CHAR(10),a.[EFFDT],110)) AS EFF_DATE, 
c.[EMAIL_ADDR]
FROM 
[JOB] a, [EMAIL_ADDRESSES] c
INNER JOIN 
(SELECT 
[PS_JOB].[EMPLID], 
MIN([PS_JOB].[EMPL_RCD]) AS MIN_EMPL_RCD, 
MAX([PS_JOB].[EFFSEQ]) AS MAX_EFF_SEQ, 
MAX([PS_JOB].[EFFDT]) AS MAX_EFF_DATE 
FROM 
[JOB] 
GROUP BY 
[JOB].[EMPLID]) b 
ON 
d.[EMPLID] = b.[EMPLID] 
AND d.[EFFDT] = b.MAX_EFF_DATE 
AND d.[EFFSEQ] = b.MAX_EFF_SEQ 
AND d.[EMPL_RCD] = b.MIN_EMPL_RCD
AND d.[EMPL_STATUS] = 'A'
AND d.[HR_STATUS] = 'A'
WHERE
a.[EMPLID] = c.[EMPLID]

Now, isn't this fella coming up easier to read?

SELECT 
a.EMPLID, 
a.EMPL_RCD, 
a.EMPL_STATUS, 
a.HR_STATUS, 
a.EFFSEQ, 
a.ACTION, 
(CONVERT(CHAR(10),a.[EFFDT],110)) AS EFF_DATE, 
c.[EMAIL_ADDR]
FROM 
[JOB] a INNER JOIN [EMAIL_ADDRESSES] c
ON a.[EMPLID] = c.[EMPLID]
INNER JOIN 
(SELECT 
[PS_JOB].[EMPLID], 
MIN([PS_JOB].[EMPL_RCD]) AS MIN_EMPL_RCD, 
MAX([PS_JOB].[EFFSEQ]) AS MAX_EFF_SEQ, 
MAX([PS_JOB].[EFFDT]) AS MAX_EFF_DATE 
FROM 
[JOB] 
GROUP BY 
[JOB].[EMPLID]) b 
ON 
d.[EMPLID] = b.[EMPLID] 
AND d.[EFFDT] = b.MAX_EFF_DATE 
AND d.[EFFSEQ] = b.MAX_EFF_SEQ 
AND d.[EMPL_RCD] = b.MIN_EMPL_RCD
AND d.[EMPL_STATUS] = 'A'
AND d.[HR_STATUS] = 'A'

Isn't that better?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

You can't have TWO type of JOINs in a single query (or at least I thing so, never check this)
What happens with:

[COLOR=blue]SELECT[/color] a.EMPLID,
       a.EMPL_RCD,
       a.EMPL_STATUS,
       a.HR_STATUS,
       a.EFFSEQ,
       a.ACTION,
       ([COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]CHAR[/color](10),a.[EFFDT],110)) [COLOR=blue]AS[/color] EFF_DATE,
       c.[EMAIL_ADDR]
[COLOR=blue]FROM[/color] [JOB] a
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] [PS_JOB].[EMPLID],
                   [COLOR=#FF00FF]MIN[/color]([PS_JOB].[EMPL_RCD]) [COLOR=blue]AS[/color] MIN_EMPL_RCD,
                   [COLOR=#FF00FF]MAX[/color]([PS_JOB].[EFFSEQ]) [COLOR=blue]AS[/color] MAX_EFF_SEQ,
                   [COLOR=#FF00FF]MAX[/color]([PS_JOB].[EFFDT]) [COLOR=blue]AS[/color] MAX_EFF_DATE
            [COLOR=blue]FROM[/color] [JOB]
            [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] [JOB].[EMPLID]) b
[COLOR=blue]ON[/color] [COLOR=blue]d[/color].[EMPLID] = b.[EMPLID]
   AND [COLOR=blue]d[/color].[EFFDT] = b.MAX_EFF_DATE
   AND [COLOR=blue]d[/color].[EFFSEQ] = b.MAX_EFF_SEQ
   AND [COLOR=blue]d[/color].[EMPL_RCD] = b.MIN_EMPL_RCD
   AND [COLOR=blue]d[/color].[EMPL_STATUS] = [COLOR=red]'A'[/color]
   AND [COLOR=blue]d[/color].[HR_STATUS] = [COLOR=red]'A'[/color]
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] [EMAIL_ADDRESSES] c [COLOR=blue]ON[/color] a.[EMPLID] = c.[EMPLID]) [COLOR=blue]d[/color]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[emmy]

Thank you to both of you. The query is now working great, and I've learned a little more about the mysterious world of SQL.

 

[AlexCuse]

Glad you got it working

I hope you used boris' version, or my second one. That first one was juste meant for an example of how awful it would be to mix the two join styles ;-)

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

Yuck... I thought I'd try something just for laughs. It works!

select * from
(select a = 1 union select 2) x, (select a = 1 union select 2) y
where x.a = y.a

And evidently you cannot mix join styles.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[bborissov]

Why you think this shouldn't work?
You join two derived tables. This is equal to:

select *
      from (select a = 1 union select 2) x
INNER JOIN (select a = 1 union select 2) y ON x.a = y.a

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

I didn't try actually running the query with the mixed join styles, but it did parse alright (SQL 2k). Its a terrible idea though (IMHO). I would probably have a seizure if I stumbled on a query that looked like that in one of my db's

Ignorance of certain subjects is a great part of wisdom

 

[emmy]

Well, I discovered that some people were missing. So back to the drawing board! These two queries work when run independent of each other, but I've messed up the join again. It says it can't find the column 'MAX_EFF_DATE'. If I take that out, it still runs but only if I increase the timeout by 3 fold.
If I can just get everyone's last record out of the database (max date, max sequence, min record) then I can add in the rest of the fields I need to see.
Here is the new mangled mess, thanks for looking at it. I spent WAY too much of my youth using Access's grid - now I have to write real SQL...

SELECT
a.EMPLID,
(CONVERT(CHAR(10),MAX(a.[EFFDT]),110)) AS MAX_EFF_DATE
FROM PS_JOB a
INNER JOIN [PS_JOB] b
ON a.[EMPLID] = b.[EMPLID]
INNER JOIN
(
SELECT
PS_JOB.EMPLID,
PS_JOB.EFFDT,
Max(PS_JOB.EFFSEQ) AS MAX_EFFSEQ,
Min(PS_JOB.EMPL_RCD) AS MIN_EMPL_RCD
FROM
[PS_JOB]
GROUP BY
PS_JOB.EMPLID,
PS_JOB.EFFDT
) c
ON
a.[EMPLID] = c.[EMPLID]
AND a.[MAX_EFF_DATE] = c.[EFFDT]
WHERE (((a.EMPL_RCD)=0))
GROUP BY a.EMPLID