Solved

Join help

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

867 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

15 Experts available now in Live!

Get 1:1 Help Now