Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Join help

Posted on 2012-03-21
1
Medium Priority
?
243 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 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

916 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