Solved

SQL Join Issue

Posted on 2013-01-14
2
323 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
ID: 38775842
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
ID: 38775915
Yep, I copied the inner join query I was using to compare.

However, your subquery solution did the trick.  Thanks!
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

Suggested Solutions

Title # Comments Views Activity
html input clean up 3 47
[MYSQL]: Delete is very slow 4 54
myqsl update statement on phpMyAdmin 8 22
update joined tables 2 31
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
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.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

920 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

14 Experts available now in Live!

Get 1:1 Help Now