Solved

SQL Join Issue

Posted on 2013-01-14
2
320 Views
Last Modified: 2013-01-14
In MySQL I'm working with a database table called Events I didn't design and can't change which has a field EventTime which is a varchar(8) which has entries like '9:00 AM', '11:00 AM', '3:00 PM'.  The table only has entries for certain times during the day.

I need to create a report for a particular day (in essence a day calendar) which lists all the times in ascending order whether or not they have corresponding records in the Events table, but if there is one it should show up.

Ex:

9:00 AM   Event1
10:00 AM  
11:00 AM  Event2
12:00 PM
1:00 PM
2:00 PM Event3
...
6:00 PM

I have created a table TimeOrder with the various text fields of time with an ID field so that I can order properly:

ID                         TimeValue
1                           9:00 AM
2                           10:00 AM
3                           11:00 AM etc

I'm trying to create a left join statement so that I see all the records in TimeOrder (i.e. the full day with all timeslots) with matching records from the Events table when there are any. Here's my SQL Statement

SELECT e.Date, t.TimeValue, e.EventType, trim(e.FName) + trim(e.LName), e.AttCount
FROM TimeOrder t
Inner JOIN enrolled e on t.timevalue = e.eventtime
WHERE e.Date = '2013-01-01'
ORDER BY t.ID

Open in new window


However my output only has the timeslots with corresponding records in Event (similar to an inner join).

Any thoughts?
0
Comment
Question by:deedub84
2 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
Comment Utility
Your query IS using INNER JOIN, try with LEFT JOIN, and you could also try

SELECT e.Date, t.TimeValue, e.EventType, trim(e.FName) + trim(e.LName), e.AttCount
FROM TimeOrder t
left JOIN (select * from enrolled WHERE Date = '2013-01-01') e on t.timevalue = e.eventtime
ORDER BY t.ID
0
 
LVL 1

Author Comment

by:deedub84
Comment Utility
Yep, I copied the inner join query I was using to compare.

However, your subquery solution did the trick.  Thanks!
0

Featured Post

How to run any project with ease

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

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

10 Experts available now in Live!

Get 1:1 Help Now