Solved

MYSQL JOIN QUERY

Posted on 2011-03-04
12
214 Views
Last Modified: 2012-05-11
Hi

I have the following query that I use to print a report that gives me all policies sold during a period of time per store.

SELECT tblpolicies.PolicyNumber
     , tblpolicies.StoreId
     , tblpolicies.ConsultantFullName
     , tblpolicies.DateReceived
     , tblpolicies.ClientFullName
     , tblpolicies.Comment
     , tblpolicies.Query
     , tblpolicies.PolicyStatus
     , tblpolicies.DateModified
     , Groups.GroupName
     , Groups.StoreName
     , Groups.StoreTarget
     , Groups.StoreManager
     , Groups.PortfolioName
     , Groups.StoreStatus
     , Groups.RepName
  FROM tblpolicies
LEFT OUTER
  JOIN ( SELECT StoreId,
                StoreName, StoreManager, GroupName, StoreTarget, PortfolioName, StoreStatus, RepName 
           FROM tblstores
         GROUP
             BY StoreId ) AS Groups
    ON tblpolicies.StoreId = Groups.StoreId
WHERE DateReceived BETWEEN '2011-01-01' AND '2011-01-31'
AND RepName='{$_SESSION['RepNameReport']}' AND StoreStatus='ACTIVE' ORDER BY StoreId, DateReceived") or die(mysql_error());

Open in new window


This works great, however I need to also count the amount of policies done per store.
How to I change this query?
0
Comment
Question by:fabzster187
  • 5
  • 2
  • 2
  • +2
12 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 35037121
try the below,

if that doesn't work, please post some sample data and the expected result.
SELECT a.PolicyNumber
     , a.StoreId
     , a.ConsultantFullName
     , a.DateReceived
     , a.ClientFullName
     , a.Comment
     , a.Query
     , a.PolicyStatus
     , a.DateModified
     , b.GroupName
     , b.StoreName
     , b.StoreTarget
     , b.StoreManager
     , b.PortfolioName
     , b.StoreStatus
     , b.RepName
     , (select count(policynumber) from tblpolicies where StoreID = a.StoreID) as countperstore
  FROM tblpolicies a
LEFT OUTER
  JOIN ( SELECT StoreId,
                StoreName, StoreManager, GroupName, StoreTarget, PortfolioName, StoreStatus, RepName 
           FROM tblstores
         GROUP BY StoreId ) AS b
    ON a.StoreId = b.StoreId
WHERE DateReceived BETWEEN '2011-01-01' AND '2011-01-31'
AND RepName='{$_SESSION['RepNameReport']}' AND StoreStatus='ACTIVE' ORDER BY StoreId, DateReceived") or die(mysql_error());

Open in new window

0
 
LVL 14

Expert Comment

by:Scott Madeira
ID: 35039285
the query above should give you total policies by store but it will do so on every line.  For example, if store id sold 4 policies then you would 4 rows in your results (one for the details of each policy) and the countperstore column of all 4 rows would be 4.  If you exported this to excel and did a sum of your policies there would be lots of double-counting.  If that isn't an issue then it should work.  The problem is that you are trying to get summary (per store) data in the same query as detailed data (tell me about each policy.)

I would suggest that you go with two separate queries (may need to check syntax on the summary query that I posted below:

 
SELECT tblstores.StoreName,
       count(policynumber) as 'Policies'

  FROM tblpolicies
       LEFT OUTER JOIN tblstores ON tblpolicies.StoreId = tblstores.StoreId
WHERE DateReceived BETWEEN '2011-01-01' AND '2011-01-31'
AND RepName='{$_SESSION['RepNameReport']}' AND StoreStatus='ACTIVE' ORDER BY StoreId, DateReceived") or die(mysql_error());
group by
   tblstores.Storename

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35039546
You can also try like this.
SELECT tblpolicies.PolicyNumber, 
       tblpolicies.StoreId, 
       tblpolicies.ConsultantFullName, 
       tblpolicies.DateReceived, 
       tblpolicies.ClientFullName, 
       tblpolicies.COMMENT, 
       tblpolicies.Query, 
       tblpolicies.PolicyStatus, 
       tblpolicies.DateModified, 
       Groups.GroupName, 
       Groups.StoreName, 
       Groups.StoreTarget, 
       Groups.StoreManager, 
       Groups.PortfolioName, 
       Groups.StoreStatus, 
       Groups.RepName, 
       t1.Cnt_policynumber 
  FROM tblpolicies 
       INNER JOIN (  SELECT StoreID, 
                      COUNT(policynumber) Cnt_policynumber 
                 FROM tblpolicies 
             GROUP BY StoreID) AS t1 
         ON tblpolicies.StoreID = t1.StoreID 
       LEFT OUTER JOIN (  SELECT StoreId, 
                                 StoreName, 
                                 StoreManager, 
                                 GroupName, 
                                 StoreTarget, 
                                 PortfolioName, 
                                 StoreStatus, 
                                 RepName 
                            FROM tblstores 
                        GROUP BY StoreId) AS Groups 
         ON tblpolicies.StoreId = Groups.StoreId 
 WHERE DateReceived BETWEEN '2011-01-01' AND '2011-01-31'
   AND RepName='{$_SESSION['RepNameReport']}' 
   AND StoreStatus='ACTIVE' ORDER BY StoreId, DateReceived") 
    OR die(mysql_error());

Open in new window

0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 3

Author Comment

by:fabzster187
ID: 35042132
My problem is that query is used for a report that prints to pdf

So I need a total per store to be printed out on each heading
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35043470
You can use my query to create your report, but the formatting has to be done with PHP, not the query.

I would suggest you take a look at this link
http://www.phpfreaks.com/forums/mysql-help/calculating-grand-total-from-multiple-queries/
0
 
LVL 3

Author Comment

by:fabzster187
ID: 35053855
Yeah I can format the total in PHP on the report no problem.

I tested the above queries and they give me the incorrect totals though (they are way out)

for example

the one group did only 17 policies in total and a store in that group who only did 1 policy shows that the total is 617.

Any more info I can provide to assist
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35053905
Provide some sample data with expected result.
0
 
LVL 3

Author Comment

by:fabzster187
ID: 35066493
ok

I use the above query currently to give me results for a report that will give me all the stores that sold policies from a group during a period.

store name
store details (managers etc)
then below that I print in PHP the actual policies in a list (attached pdf) now I would like the
totals for the specific store to be displayed in the header of the store(as long as I get the result from the Mysql query I can place it there with php)

I exported some data to excel from the tblstores and tblpolicies tables so that you may see the structure of the data.

Hope this helps




daily-report.pdf
tblstores.xls
tblpolicies.xls
0
 
LVL 3

Author Comment

by:fabzster187
ID: 35092236
more info supplied, why no response?

please assist guy's  I am trying but cannot solve this
0
 
LVL 3

Author Comment

by:fabzster187
ID: 35105962
Ok I solved this myself...

here is the solution:

SELECT tblpolicies.PolicyNumber
     , tblpolicies.StoreId
     , tblpolicies.ConsultantFullName
     , tblpolicies.DateReceived
     , tblpolicies.ClientFullName
     , tblpolicies.Comment
     , tblpolicies.Query
     , tblpolicies.PolicyStatus
     , tblpolicies.DateModified
     , Groups.GroupName
     , Groups.StoreName
     , Groups.StoreTarget
     , Groups.StoreManager
     , Groups.PortfolioName
     , Groups.StoreStatus
     , Groups.RepName
     , Policies.total_policies
  FROM tblstores AS Groups
INNER
  JOIN tblpolicies
    ON tblpolicies.StoreId = Groups.StoreId
   AND tblpolicies.PolicyStatus='ACTIVE'
   AND tblpolicies.DateReceived BETWEEN '{$_SESSION['StartDateRepReport']}'
                                    AND '{$_SESSION['EndDateRepReport']}'
INNER
  JOIN ( SELECT StoreId
              , COUNT(*) AS total_policies
           FROM tblpolicies
          WHERE DateReceived BETWEEN '{$_SESSION['StartDateRepReport']}'
                                 AND '{$_SESSION['EndDateRepReport']}'
                                 AND tblpolicies.PolicyStatus = 'ACTIVE'
         GROUP
             BY StoreId ) AS Policies
    ON Policies.StoreId = Groups.StoreId
 WHERE Groups.RepName='{$_SESSION['RepNameReport']}'
   AND Groups.StoreStatus='ACTIVE'
ORDER 
    BY Groups.StoreId
     , tblpolicies.DateReceived

Open in new window

0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 37313444
Question PAQ'd and stored in the solution database.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question