Solved

How to write SELECT statememt for two tables which contain dates that need to be displayed

Posted on 2011-09-15
14
318 Views
Last Modified: 2012-05-12
How can I get the query to output the following
Date                 AgentID       Ans        Aban      Sales      Quotes
1/1/2011           711              22         11                2                 5
1/2/2011           711              24          6                1                 4
1/3/2011           711              27          7                0                 0
1/4/2001           711               0           0                3                 6
1/5/2011           711              21          8                1                 3



if the tables are as follows

tblCalls                                                          tblSales
Date               AgentID      Ans      Aban            Date             AgentID      Sales      Quotes
1/1/2011       711               22        11               1/1/2011       711              2                  5
1/2/2011       711               24        6                1/2/2011       711               1                  4
1/3/2011       711               27        7                1/4/2001       711               3                  6
1/5/2011       711               21        8                1/5/2011       711               1                 3

*************************************************************************
The issue is that there is data for a date *eg 01/04/2011) in tblSales in which there is no date in tblCalls, yet calls is the main table. How can I get the 01/04/2011 to be part of the recordset so I can display the date in HTML report?

How should the SELECT statement be worded as to be able to show the date eg 01/04/2011. Normally the SELECT would say

SELECT tblCalls.Date, tblCalls.Ans, tblCalls.Aban, tblSales.Sales, tblSales.Quotes FROM tblCalls, tblSales WHERE tblCalls.AgentID = tblSales.AgentID and tblCalls.AgentID=711

But this would not display the desired Output in HTML because there is no tblCalls.Date for 01/04/2011.

Thanks for your help
0
Comment
Question by:gbeaulac
  • 3
  • 3
  • 2
  • +4
14 Comments
 
LVL 8

Expert Comment

by:pilson66
ID: 36547300
select c.Date, c.AgentID, c.Ans, c.Aban, s.Sales, s.Quotes from tblCalls c, tblSales s where c.AgentID = s.AgentID and c.AgentID = 711;
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 36547301
I think one way is to create a table that has all the dates then do an outer join with it i.e.

select tblDates.Date, tblCalls.Ans, tblCalls.Aban, tblSales.Sales, tblSales.Quotes
FROM tblDates left outer join tblCalls on (tblDates.date=tblCalls.Date)
left outer join tblSales on (tblDates.date=tblSales.Date)
WHERE tblCalls.AgentID = tblSales.AgentID and tblCalls.AgentID=711

Note: untested.
0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36547580
select isnull(tblCalls.Date,tblSales.Date) Date, isnull(tblCalls.agentId,tblSales.agentId) agentId, isnull(tblCalls.Ans,0) Ans, isnull(tblCalls.Aban,0) Aban,
isnull(tblSales.Sales,0) Sales, isnull(tblSales.Quotes,0) Quotes
from tblCalls full outer join tblSales on tblCalls.AgentID = tblSales.AgentID and tblSales.Date = tblCalls.Date
0
 
LVL 4

Expert Comment

by:h4hardy
ID: 36547721
hi,

you can check this ,

select date,AgentID,Sales,Quotes from tblsales
Union
select date,AgentID,Ans,Aban from tblcalls group by date order by date 12:26 
SELECT tblsales.date, tblsales.AgentID, Ans, Aban, Sales, Quotes
FROM tblsales
LEFT JOIN tblcalls ON tblsales.date = tblcalls.date
UNION SELECT tblcalls.date, tblcalls.AgentID, Ans, Aban, Sales, Quotes
FROM tblcalls
LEFT JOIN tblsales ON tblcalls.date = tblsales.date
WHERE Sales IS NULL
ORDER BY date
LIMIT 0 , 30  

Open in new window

0
 
LVL 14

Expert Comment

by:Scott Madeira
ID: 36549133
Try this:

 
SELECT 
	tblCalls.Date, 
	tblCalls.Ans, 
	tblCalls.Aban, 
	tblSales.Sales, 
	tblSales.Quotes 
FROM 
	tblCalls left outer join tblSales on (tblCalls.AgentID = tblSales.AgentID )
WHERE 
	tblCalls.AgentID=711
order by
	tblCalls.Date

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36552188
For starters, you probably want to learn about how to use the ISO-8601 DATETIME format.  This article will tell you all about it.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Also, please don't use MySQL reserved words like "date" for the name of your columns.  You will eventually run into confusion with something like that.

It might help to see the CREATE TABLE statements for these tables so we can see how the fields are defined.  Might want to think about posting that information if you have more questions about this.

I am also wondering if these tables should really be one table.  I think the data base would remain normalized.  And if you combined the tables it might make programming easier.  Just a thought...

I think smadeira's query will probably work.  This might work too.  Not sure there will be any meaningful difference in performance.

Best regards to all, ~Ray
SELECT 
	tblCalls.Date, 
	tblCalls.Ans, 
	tblCalls.Aban, 
	tblSales.Sales, 
	tblSales.Quotes 
FROM 
	tblCalls, tblSales 
WHERE
        tblCalls.AgentID = tblSales.AgentID 
AND 
	tblCalls.AgentID=711
ORDER BY
	tblCalls.Date

Open in new window

0
 
LVL 4

Expert Comment

by:h4hardy
ID: 36553171
Hi Ray_Paseur ,

above both query are not work anymore and returns the wrong result.. check the attached file

worng result
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36554169
Not work and wrong result don't really help us understand what you want.  Maybe you want to add the tblCalls.AgentID to the SELECT list?
0
 
LVL 14

Expert Comment

by:Scott Madeira
ID: 36554599
Try this...

 
SELECT 
	tblCalls.Date, 
	tblCalls.Ans, 
	tblCalls.Aban, 
	tblSales.Sales, 
	tblSales.Quotes 
FROM 
	tblCalls left outer join tblSales on (tblCalls.AgentID = tblSales.AgentID  and  tblCalls.Date = tblSales.Date )
WHERE 
	tblCalls.AgentID=711
order by
	tblCalls.Date

Open in new window


Also, is it possible that there is more than one row in either table for a given agent and date?  Your initial post implies that agent 711 has one row in each table for any given date.  If there is more than one row in the table then you would need to do a sum() on the columns for which you want totals.

Something like this:

SELECT
      tblCalls.Date,
        tblCalls.AgentID,
      sum(tblCalls.Ans),
      sum(tblCalls.Aban),
      sum(tblSales.Sales),
      sum(tblSales.Quotes)
FROM
      tblCalls left outer join tblSales on (tblCalls.AgentID = tblSales.AgentID  and  tblCalls.Date = tblSales.Date )
WHERE
      tblCalls.AgentID=711
GROUP BY
        tblCalls.Date, tblCalls.AgentID
order by
      tblCalls.Date
0
 

Author Comment

by:gbeaulac
ID: 36554940
So far the closest has been johanntagle.

I am still testing, the actual challenge is much more complex than I gave, but johanntagle provided an alternative that promised potential.

I will continue testing.

Thanks everyone for your help. I will be back to give final decision soon.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36555179
... the actual challenge is much more complex than I gave, but ...

Is that really the case?  In the future please do not waste anyone's time with inappropriately over-simplified examples.  Post the actual test data and the actual desired output.  You will probably be surprised how quickly you will get the actual answer, if you tell us the truth about what you have and what you want.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36555355
Well maybe the author only posted the part he is having problems with.  I for one appreciate it when the author takes the time to not dump all information on you - shows he's really doing his work.  As long as he keeps it that way and doesn't suddenly dump the bigger problem later, I think its okay.
0
 

Author Closing Comment

by:gbeaulac
ID: 36555409
After testing it as is, it did not yield what I was looking for, but it certainly provided direction for my research, I finally found the missing piece at this link :
 http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql

where it talked about FULL OUTER JOIN which is not available to mysql, wherein it shows a brilliant alternative using "UNION". This was perfect to add to johanntagle's solution.

PS With this solution it will not be necessary to create a table for dates.

Thank you Johanntagle for your patience and solution.
0
 

Author Comment

by:gbeaulac
ID: 36555416
Oh, I almost forgot ... @ray_paseur


You have succeeded in getting impressive certifications on many topics and I think that is commendable. May I suggest though that you try and get certifications in other intangibles like a) not jumping to conclusions and b) developing your leadership skills.

Your comment about wasting people's time was obviously off-the-mark not to mention uncalled for as johanntagle solved my problem without further specs. You are not obligated to answer my questions, just as I am not obligated to use EE.
0

Featured Post

Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Creating and Managing Databases with phpMyAdmin in cPanel.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

757 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

23 Experts available now in Live!

Get 1:1 Help Now