Solved

Join help

Posted on 2012-03-21
1
236 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

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.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL ERROR 1045 (28000) 2 90
PHP loop not working 4 71
unable to insert record into a table 2 37
MySQL InnodDB Import from mysqldump takes forever. 2 32
Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

791 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