Getting Unique Data From Two Tables

Question

Getting Unique Data From Two Tables

wilkerson4492

Newbie

Points: 8
More actions
April 26, 2010 at 6:07 pm

#231992

I currently have two tables containing part data as follows:
Table1
=========
Year
Month
Day
Hour
Good_Parts
Bad_Parts
Table2
=========
Year
Month
Day
Hour
Run_Time
Fault_Time
Idle_Time
- The above data is logged once an hour.
- I need to be able to query the data based on year, month, and day.
- The results I am looking for would display the good parts, bad parts, run time, fault time, and idle time for each hour of the selected day, month, and year.
- Does anyone have any idea how to code a query for this? If I had a unique ID in each table I know I could use a JOIN and that would make life easy but I do not have that and when I try using a JOIN, I end up with more than the expected amount of data. I would expect to get a result for only each hour for a total of 24 sets of data.
Thanks,
Wilks

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

I'm venturing a guess that you might not be familiar with creating a join on more than one column...like you said, it's intuitive if you had a unique column.

what might not be so obvious is your design is "unique" if you look at 4 columns at the same time...all your date-ish columns.

i think this might be what you are looking for:

select

--since the four t2 columns will be the same, no need to display them, they just duplicate t1

t1.Year

t1.Month

t1.Day

t1.Hour

t1.Good_Parts

t1.Bad_Parts

t2.Run_Time

t2.Fault_Time

t2.Idle_Time

FROM Table1 t1

LEFT OUTER JOIN Table2 t2

ON t1.Year = t2.Year

AND t1.Month = t2.Month

AND t1.Day = t2.Day

AND t1.Hour = t2.Hour

ORDER BY

t1.Year

t1.Month

t1.Day

t1.Hour

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

wilkerson4492 Newbie Points: 8 More actions · Answer 2

Thanks Lowell for your quick response. That gave me exactly the results I was looking for. I have to say that getting my head around the various types of JOINS has been one of the more interesting and challenging aspects of learning SQL.

Thanks again for your help,

Wilks.