Solved

Join help

Posted on 2012-03-21
1
233 Views
Last Modified: 2012-06-27
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
Comment
Question by:Dan560
1 Comment
 
LVL 6

Accepted Solution

by:
netjgrnaut earned 500 total points
ID: 37746921
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now