Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

Join help

Hi,

I am running a helpdesk system with mysql 4.0.16. At the end of every day a PHP script captures / imports the total number of calls by priority and status.

I need to build a report that shows this.

The problem I have is if that no one logs a High priority call then it will not show in my report. My report needs to show all priorities , I would then configure the report to show a zero if there were no calls for a certain priority.

 So I created a new table with a list of priorities, which included UK-HIGH, UK-MEDIUM, UK-LOW, UK-ONHOLD

I then left joined this table to the table that has the stored amount of calls. My statement looks like this -

Supportworks Sql> SELECT
    -> dailyreport_priority.priority,
    -> FROM_UNIXTIME(timex,"%Y-%m-%d") as Date,
    -> snapshot.fk_company_id,
    -> snapshot.amount,
    -> snapshot.priority
    -> snapshot
    -> left join dailyreport_priority on snapshot.priority =  dailyreport_priority.priority
    -> where fk_company_id = "abc uk"
    -> and FROM_UNIXTIME(timex,"%Y-%m-%d") BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
    -> ;
+--------------+------------+---------------+--------+--------------+

Open in new window


The outcome was this:

+--------------+------------+---------------+--------+--------------+
| priority     | Date       | fk_company_id | amount | priority     |
+--------------+------------+---------------+--------+--------------+
| UK- Low  | 2012-02-22 | ABC UK       |      2 | UK- Low  |
| UK- Low  | 2012-02-23 | ABC UK       |      2 | UK- Low  |
| UK- Low  | 2012-02-24 | ABC UK       |      1 | UK- Low  |
| UK- Low  | 2012-03-06 | ABC UK       |      3 | UK- Low  |
| UK- Low  | 2012-03-07 | ABC UK       |      2 | UK- Low  |
| UK- Low  | 2012-03-08 | ABC UK       |      4 | UK- Low  |
| UK- Low  | 2012-03-09 | ABC UK       |      6 | UK- Low  |
| UK- Low  | 2012-03-10 | ABC UK       |      3 | UK- Low  |
| UK- Low  | 2012-03-11 | ABC UK       |      3 | UK- Low  |
| UK- Low  | 2012-03-12 | ABC UK       |      3 | UK- Low  |
| UK- Low  | 2012-03-14 | ABC UK       |      5 | UK- Low  |
| UK- Low  | 2012-03-15 | ABC UK       |      2 | UK- Low  |
| UK- Low  | 2012-03-16 | ABC UK       |      3 | UK- Low  |
| UK- Low  | 2012-03-17 | ABC UK       |      1 | UK- Low  |
| UK- Low  | 2012-03-18 | ABC UK       |      1 | UK- Low  |
| UK- Low  | 2012-03-19 | ABC UK       |      1 | UK- Low  |
| UK- High | 2012-03-21 | ABC UK       |      1 | UK- High |
+--------------+------------+---------------+--------+--------------+

Open in new window


 I thought a left join would show all of my priorities.
Can anyone help?
0
Dan560
Asked:
Dan560
1 Solution
 
netjgrnautCommented:
If I'm reading your (implied) FROM correctly in your query...

-> (FROM) snapshot
-> left join dailyreport_priority on snapshot.priority =  dailyreport_priority.priority

Open in new window


...then you have your join backwards.  You want a RIGHT join to show all the rows in the *second* table (dailyreport_priority).

Refer to this tutorial...
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now