SQL Join is failing

when I test this in phpMyAdmin I get       

SELECT  cc.cost_center_name,  cc.qualified_percent,  cc.qualified_percent as qualified_amount
        FROM cost_center as cc,employees as e
        WHERE e.email='$email' AND e.campaign='$campaign'
        AND cc.email='$email' AND cc.campaign='$campaign'
        LEFT OUTER JOIN cost_center ON cc.cost_center_name=e.department

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT OUTER JOIN ON cost_center ON cc.cost_center_name=e.department LIMIT 0, 25' at line 5

Any idea why?

proof that an old dog can learn new tricks

@richb201,

Maybe what you want is a Left Join. Which pulls all records from left table plus the records that equal to it in the right table.

I am frustrated by this T-Sql. I have a bunch of these queries because there are so many tables. Each row has a userid and campaign that is used for filtering. So having 29 queries is really slowing down my system. My plan is to create a stored procedure to increase speed and give me better control.

proof that an old dog can learn new tricks

@richb201,

Also don't forget about database views. It can help you cut down on coding as well (because most of the query will be in the database).

Thks. I am just not sure what a view gets me? I have this example query (one of about 20):
        SELECT  cc.cost_center_name,  cc.qualified_percent,  SUM(e.w2_wages) as qualified_amount
        FROM cost_center as cc,employees as e
        WHERE e.email='$email' AND e.campaign='$campaign'
        AND cc.email='$email' AND cc.campaign='$campaign'
        AND cc.cost_center_name=e.department

I'd like to sum w2_wages in the employees table for each of the cost centers that appear in the cost_center table. What I get when i run this is:

cost_center_name qualified_percent qualified_amount
test 0 267,000

There is a cost_center called test, but there are about 30 other ones too. Why is this only running on one row? I thought this would go through each cost center and do the same thing. At least with stored procedure I can use "FOR each". My thought right now is to use a SP to build about 20 "temporary tables", which pretty much mimic the data needed in the final spreadsheet. Since I need to do this twice, once for the spreadsheets and once for the pdf report. I figure they could both share a single SP. Running through my inline Query Statement as it stands runs about 9 seconds add this is on my local server. That is pretty slow considering? I might need to index some of the columns, but I am concerned about the lack of "control" using inline-SQL statements. I admit this could be my lack of knowledge.

proof that an old dog can learn new tricks

@richb201,

If this was a view then this is what you get....
Your Large SQL statement would be in the database as... view_center_sum
Your code would be something like this...
Select cost_center_name, qualified_amount
FROM view_center_sum
...see the difference. Not only is it less code but you know for a fact that it works.


in regards to your question about getting the sum for all centers...You need to add a GROUP BY in your SQL statement. This will group the results by call center and then sum it for you.

That worked great. I also have some complex calculations to do with data that is in tables in mySQL. I feel that trying to write them in a "single" SQL statement is really too much, thus stored procedures.

proof that an old dog can learn new tricks

That worked great. I also have some complex calculations to do with data that is in tables in mySQL. I feel that trying to write them in a "single" SQL statement is really too much, thus stored procedures.

AWESOME!! I'm glad that it worked out for you.  As far as store procedures goes...if that is what works for you then go for it.