Multiple inner joins on the same table

[rickpill]

I have the following table:

CampaignID AccountNumber
1................ 1
1................ 3
1................ 7
2................ 2
2................ 3
2................ 7
3................ 1
3................ 3
3................ 7

The Account must exist for all 3 campaigns, in the above example, accounts 3 and 7 qualify only. This is what I've come up with, but I get a syntax error.

SELECT
c1.AccountNumber, c2.AccountNumber, c3.AccountNumber
FROM
uCampaign as c1
WHERE
c1.CampaignID = '1'
INNER JOIN
uCampaign as c2
ON
c1.AccountNumber = c2.AccountNumber
INNER JOIN
uCampaign as c3
WHERE
c2.CampaignID = '2'
ON
c1.AccountNumber = c3.AccountNumber
WHERE
c3.CampaignID = '3'


Thanks.
Rick

 

[ldandy]

You should put all tables and inner joins (together with the ON clauses) in the FROM clause (which will be only one) and all conditions in a single WHERE clause (and link them with AND).

 

[rickpill]

Thanks Idandy,

I've taken another approach to resolving this using HAVING:

SELECT AccountNumber
FROM uCampaign
WHERE CampaignID = '1' .... 2 or 3
GROUP BY AccountNumber
HAVING COUNT(AccountNumber) = 3;

It works, but I wonder if it's the better approach than INNER JOINS.

Rick

 

[ldandy]

Rick,

I was pointing only where the syntax error come from. Of course your second approach is faster and "cleaner".

Danny.

 

[rickpill]

Thanks Danny,

I don't know if I mentioned this before, but I'm new to SQL - I guess you must've guessed this by my poorly structured join request. I realize, from your viewpoint, you answered the question as it was posed.


Rick

 

[DesertTrip]

ldandy, or anyone else ready this who would know what he meant. You stated that he "should put all tables and inner joins (together with the ON clauses) in the FROM clause (which will be only one) and all conditions in a single WHERE clause (and link them with AND)."

I logged onto night hopig to find out how to do this and your explanation is kind of helping. However, I am very SQL challenged.

Let's say I have two joins that if written singlely, they'd be as follows:

FROM tblCaseData INNER JOIN lkpSpcProjects ON tblCaseData.[ProjectID] = lkpSpcProjects.[ProjectID]

AND

FROM tblCaseData INNER JOIN lkpEmployees ON tblCaseData.EmployeeNumber = lkpEmployees.EmployeeNumber

Would I then write this out as:

FROM tblCaseData INNER JOIN lkpSpcProjects ON tblCaseData.[ProjectID] = lkpSpcProjects.[ProjectID] AND FROM tblCaseData INNER JOIN lkpEmployees ON tblCaseData.EmployeeNumber = lkpEmployees.EmployeeNumber ?

Thanks in advance for any assistance I can get on this.

 

[DesertTrip]

I don't know if an old post will bring any results, but I was searching for answers on the search function to try and avoid having to being a new post. My misfortune is not being entirely clear on the basics of SQL.

 

[r937]

rick, your GROUP BY HAVING count(*)=3 is the better way

deserttrip, you were close--

SELECT foo
  FROM tblCaseData 
INNER 
  JOIN lkpSpcProjects 
    ON tblCaseData.[ProjectID] 
     = lkpSpcProjects.[ProjectID] 
INNER
  JOIN lkpEmployees 
    ON tblCaseData.EmployeeNumber 
     = lkpEmployees.EmployeeNumber

most databases read these clauses left to right, like above, while some want you to nest them with parentheses, like this --

SELECT foo
  FROM 
       (  tblCaseData 
  INNER 
    JOIN lkpSpcProjects 
      ON tblCaseData.[ProjectID] 
       = lkpSpcProjects.[ProjectID]  )
INNER
  JOIN lkpEmployees 
    ON tblCaseData.EmployeeNumber 
     = lkpEmployees.EmployeeNumber

rudy

 

[DesertTrip]

Thanks, Rudy...silly question. What does "foo" represent in your example?

I will go try this now....everything I have tried so far ends up in syntax errors.

 

[r937]

http://www.tuxedo.org/~esr/jargon/html/entry/foo.html

in this example it meant "whatever column(s) you're selecting"

 

[DesertTrip]

never mind on the foo question (oops)

I'lllet you know how this works.

 

[DesertTrip]

Okay, I added the word AND in between them as I was getting a really long nasty error until I did.

But, I am now getting some little syntax error : "Syntax error (missing operator) in query expression".

Can you see what might be causing it? If it means anything, after I close out the alert window, the letters in red below are highlighted.

SELECT DISTINCTROW Format$([tblCaseData].[Date],"m/yy&quot
AS DateByMonth, [lkpSpcProjects]![ProjectTitle] AS Activity,
tblCaseData.EmployeeNumber, Sum([tblCaseData]![Hours]) AS Hours,
Count(*) AS Count, Sum([tblCaseData]![Mileage]) AS Mileage,
Sum([tblCaseData]![Mileage])/40 AS MileageCalc
FROM tblCaseData
INNER JOIN lkpSpcProjects ON tblCaseData.[ProjectID] = lkpSpcProjects.[ProjectID] AND INNER JOIN lkpEmployees ON tblCaseData.[EmployeeNumber] = lkpEmployees.[EmployeeNumber]
GROUP BY Format$([tblCaseData].[Date],"m/yy&quot, [lkpSpcProjects]![ProjectTitle], tblCaseData.EmployeeNumber
HAVING (((Format$([tblCaseData].[Date],"m/yy&quot)=[Enter Month Year]))

 

[DesertTrip]

Never mind!! I got it figured out the beginners way. Sheer luck. I made a secondary query off the first, and then I brought in the lkp.Employees table, linked and added the district to my query and wallah!

My report is now showing what it needs to.

Thanks for trying to help me out!

DT