Very Easy join but can't seem to get it. help appr

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Very Easy join but can't seem to get it. help appr

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-22 : 14:59:38

table: morganwedcheckins

checkindate executiontime
8/13/2008 8/13/2008
8/16/2008 8/13/2008
8/20/2008 8/13/2008
8/23/2008 8/13/2008
9/10/2008 8/13/2008
9/13/2008 8/13/2008
8/20/2008 8/20/2008
8/23/2008 8/20/2008
8/27/2008 8/20/2008
8/30/2008 8/20/2008
9/17/2008 8/20/2008
9/20/2008 8/20/2008

needs to be matched with Table: Tester

hotel roomtype checkindate executiontime id_num
Clift 1 NULL 8/13/2008 1
Clift 2 NULL 8/13/2008 2
Clift 3 NULL 8/13/2008 3
Clift 4 NULL 8/13/2008 4
Clift 5 NULL 8/13/2008 5
Clift 6 NULL 8/13/2008 6
Clift 7 NULL 8/13/2008 7
Clift 8 NULL 8/13/2008 8
Clift 9 NULL 8/13/2008 9
Clift 10 NULL 8/13/2008 10
Clift 1 NULL 8/13/2008 11
Clift 2 NULL 8/13/2008 12
Clift 3 NULL 8/13/2008 13
Clift 4 NULL 8/13/2008 14
Clift 5 NULL 8/13/2008 15
Clift 6 NULL 8/13/2008 16
Clift 7 NULL 8/13/2008 17
Clift 8 NULL 8/13/2008 18
Clift 9 NULL 8/13/2008 19
Clift 10 NULL 8/13/2008 20
Clift 1 NULL 8/13/2008 21
Clift 2 NULL 8/13/2008 22
Clift 3 NULL 8/13/2008 23
Clift 4 NULL 8/13/2008 24
Clift 5 NULL 8/13/2008 25
Clift 6 NULL 8/13/2008 26
Clift 7 NULL 8/13/2008 27
Clift 8 NULL 8/13/2008 28
Clift 9 NULL 8/13/2008 29
Clift 10 NULL 8/13/2008 30
Clift 1 NULL 8/13/2008 31
Clift 2 NULL 8/13/2008 32
Clift 3 NULL 8/13/2008 33
Clift 4 NULL 8/13/2008 34
Clift 5 NULL 8/13/2008 35
Clift 6 NULL 8/13/2008 36
Clift 7 NULL 8/13/2008 37
Clift 8 NULL 8/13/2008 38
Clift 9 NULL 8/13/2008 39
Clift 10 NULL 8/13/2008 40
Clift 1 NULL 8/13/2008 41
Clift 2 NULL 8/13/2008 42
Clift 3 NULL 8/13/2008 43
Clift 4 NULL 8/13/2008 44
Clift 5 NULL 8/13/2008 45
Clift 6 NULL 8/13/2008 46
Clift 7 NULL 8/13/2008 47
Clift 8 NULL 8/13/2008 48
Clift 9 NULL 8/13/2008 49
Clift 10 NULL 8/13/2008 50
Clift 1 NULL 8/13/2008 51
Clift 2 NULL 8/13/2008 52
Clift 3 NULL 8/13/2008 53
Clift 4 NULL 8/13/2008 54
Clift 5 NULL 8/13/2008 55
Clift 6 NULL 8/13/2008 56
Clift 7 NULL 8/13/2008 57
Clift 8 NULL 8/13/2008 58
Clift 9 NULL 8/13/2008 59
Clift 10 NULL 8/13/2008 60
Clift 1 NULL 8/20/2008 61
Clift 2 NULL 8/20/2008 62
Clift 3 NULL 8/20/2008 63
Clift 4 NULL 8/20/2008 64
Clift 5 NULL 8/20/2008 65
Clift 6 NULL 8/20/2008 66
Clift 7 NULL 8/20/2008 67
Clift 8 NULL 8/20/2008 68
Clift 9 NULL 8/20/2008 69
Clift 10 NULL 8/20/2008 70
Clift 1 NULL 8/20/2008 71
Clift 2 NULL 8/20/2008 72
Clift 3 NULL 8/20/2008 73
Clift 4 NULL 8/20/2008 74
Clift 5 NULL 8/20/2008 75
Clift 6 NULL 8/20/2008 76
Clift 7 NULL 8/20/2008 77
Clift 8 NULL 8/20/2008 78
Clift 9 NULL 8/20/2008 79
Clift 10 NULL 8/20/2008 80
Clift 1 NULL 8/20/2008 81
Clift 2 NULL 8/20/2008 82
Clift 3 NULL 8/20/2008 83
Clift 4 NULL 8/20/2008 84
Clift 5 NULL 8/20/2008 85
Clift 6 NULL 8/20/2008 86
Clift 7 NULL 8/20/2008 87
Clift 8 NULL 8/20/2008 88
Clift 9 NULL 8/20/2008 89
Clift 10 NULL 8/20/2008 90
Clift 1 NULL 8/20/2008 91
Clift 2 NULL 8/20/2008 92
Clift 3 NULL 8/20/2008 93
Clift 4 NULL 8/20/2008 94
Clift 5 NULL 8/20/2008 95
Clift 6 NULL 8/20/2008 96
Clift 7 NULL 8/20/2008 97
Clift 8 NULL 8/20/2008 98
Clift 9 NULL 8/20/2008 99
Clift 10 NULL 8/20/2008 100
Clift 1 NULL 8/20/2008 101
Clift 2 NULL 8/20/2008 102
Clift 3 NULL 8/20/2008 103
Clift 4 NULL 8/20/2008 104
Clift 5 NULL 8/20/2008 105
Clift 6 NULL 8/20/2008 106
Clift 7 NULL 8/20/2008 107
Clift 8 NULL 8/20/2008 108
Clift 9 NULL 8/20/2008 109
Clift 10 NULL 8/20/2008 110
Clift 1 NULL 8/20/2008 111
Clift 2 NULL 8/20/2008 112
Clift 3 NULL 8/20/2008 113
Clift 4 NULL 8/20/2008 114
Clift 5 NULL 8/20/2008 115
Clift 6 NULL 8/20/2008 116
Clift 7 NULL 8/20/2008 117
Clift 8 NULL 8/20/2008 118
Clift 9 NULL 8/20/2008 119
Clift 10 NULL 8/20/2008 120

to create this table...

hotel roomtype checkindate executiontime
Clift 1 8/13/2008 8/13/2008
Clift 2 8/13/2008 8/13/2008
Clift 3 8/13/2008 8/13/2008
Clift 4 8/13/2008 8/13/2008
Clift 5 8/13/2008 8/13/2008
Clift 6 8/13/2008 8/13/2008
Clift 7 8/13/2008 8/13/2008
Clift 8 8/13/2008 8/13/2008
Clift 9 8/13/2008 8/13/2008
Clift 10 8/13/2008 8/13/2008
Clift 1 8/16/2008 8/13/2008
Clift 2 8/16/2008 8/13/2008
Clift 3 8/16/2008 8/13/2008
Clift 4 8/16/2008 8/13/2008
Clift 5 8/16/2008 8/13/2008
Clift 6 8/16/2008 8/13/2008
Clift 7 8/16/2008 8/13/2008
Clift 8 8/16/2008 8/13/2008
Clift 9 8/16/2008 8/13/2008
Clift 10 8/16/2008 8/13/2008
Clift 1 8/20/2008 8/13/2008
Clift 2 8/20/2008 8/13/2008
Clift 3 8/20/2008 8/13/2008
Clift 4 8/20/2008 8/13/2008
Clift 5 8/20/2008 8/13/2008
Clift 6 8/20/2008 8/13/2008
Clift 7 8/20/2008 8/13/2008
Clift 8 8/20/2008 8/13/2008
Clift 9 8/20/2008 8/13/2008
Clift 10 8/20/2008 8/13/2008
Clift 1 8/23/2008 8/13/2008
Clift 2 8/23/2008 8/13/2008
Clift 3 8/23/2008 8/13/2008
Clift 4 8/23/2008 8/13/2008
Clift 5 8/23/2008 8/13/2008
Clift 6 8/23/2008 8/13/2008
Clift 7 8/23/2008 8/13/2008
Clift 8 8/23/2008 8/13/2008
Clift 9 8/23/2008 8/13/2008
Clift 10 8/23/2008 8/13/2008
Clift 1 9/10/2008 8/13/2008
Clift 2 9/10/2008 8/13/2008
Clift 3 9/10/2008 8/13/2008
Clift 4 9/10/2008 8/13/2008
Clift 5 9/10/2008 8/13/2008
Clift 6 9/10/2008 8/13/2008
Clift 7 9/10/2008 8/13/2008
Clift 8 9/10/2008 8/13/2008
Clift 9 9/10/2008 8/13/2008
Clift 10 9/10/2008 8/13/2008
Clift 1 9/13/2008 8/13/2008
Clift 2 9/13/2008 8/13/2008
Clift 3 9/13/2008 8/13/2008
Clift 4 9/13/2008 8/13/2008
Clift 5 9/13/2008 8/13/2008
Clift 6 9/13/2008 8/13/2008
Clift 7 9/13/2008 8/13/2008
Clift 8 9/13/2008 8/13/2008
Clift 9 9/13/2008 8/13/2008
Clift 10 9/13/2008 8/13/2008
Clift 1 8/20/2008 8/20/2008
Clift 2 8/20/2008 8/20/2008
Clift 3 8/20/2008 8/20/2008
Clift 4 8/20/2008 8/20/2008
Clift 5 8/20/2008 8/20/2008
Clift 6 8/20/2008 8/20/2008
Clift 7 8/20/2008 8/20/2008
Clift 8 8/20/2008 8/20/2008
Clift 9 8/20/2008 8/20/2008
Clift 10 8/20/2008 8/20/2008
Clift 1 8/23/2008 8/20/2008
Clift 2 8/23/2008 8/20/2008
Clift 3 8/23/2008 8/20/2008
Clift 4 8/23/2008 8/20/2008
Clift 5 8/23/2008 8/20/2008
Clift 6 8/23/2008 8/20/2008
Clift 7 8/23/2008 8/20/2008
Clift 8 8/23/2008 8/20/2008
Clift 9 8/23/2008 8/20/2008
Clift 10 8/27/2008 8/20/2008
Clift 1 8/27/2008 8/20/2008
Clift 2 8/27/2008 8/20/2008
Clift 3 8/27/2008 8/20/2008
Clift 4 8/27/2008 8/20/2008
Clift 5 8/27/2008 8/20/2008
Clift 6 8/27/2008 8/20/2008
Clift 7 8/27/2008 8/20/2008
Clift 8 8/27/2008 8/20/2008
Clift 9 8/27/2008 8/20/2008
Clift 10 8/27/2008 8/20/2008
Clift 1 8/30/2008 8/20/2008
Clift 2 8/30/2008 8/20/2008
Clift 3 8/30/2008 8/20/2008
Clift 4 8/30/2008 8/20/2008
Clift 5 8/30/2008 8/20/2008
Clift 6 8/30/2008 8/20/2008
Clift 7 8/30/2008 8/20/2008
Clift 8 8/30/2008 8/20/2008
Clift 9 8/30/2008 8/20/2008
Clift 10 8/30/2008 8/20/2008
Clift 1 9/17/2008 8/20/2008
Clift 2 9/17/2008 8/20/2008
Clift 3 9/17/2008 8/20/2008
Clift 4 9/17/2008 8/20/2008
Clift 5 9/17/2008 8/20/2008
Clift 6 9/17/2008 8/20/2008
Clift 7 9/17/2008 8/20/2008
Clift 8 9/17/2008 8/20/2008
Clift 9 9/17/2008 8/20/2008
Clift 10 9/17/2008 8/20/2008
Clift 1 9/20/2008 8/20/2008
Clift 2 9/20/2008 8/20/2008
Clift 3 9/20/2008 8/20/2008
Clift 4 9/20/2008 8/20/2008
Clift 5 9/20/2008 8/20/2008
Clift 6 9/20/2008 8/20/2008
Clift 7 9/20/2008 8/20/2008
Clift 8 9/20/2008 8/20/2008
Clift 9 9/20/2008 8/20/2008
Clift 10 9/20/2008 8/20/2008

any help? I tried

select t.hotel, t.roomtype, s.checkindate, t.roomrate, t.executiontime, t.table_num

from tester as t inner join morganwedcheckins as s on t.executiontime = s.executiontime
where s.executiontime = t.executiontime

but came up with too many matches. THANK YOU

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-22 : 15:05:58

Don't put the condition in both the JOIN and the WHERE clause. Since you are using the ANSI syntax, just put it in the JOIN, meaning after ON.

You need to show us what rows need to be returned and why your current query isn't working.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-22 : 15:09:48

quote:
Originally posted by tkizer

Don't put the condition in both the JOIN and the WHERE clause. Since you are using the ANSI syntax, just put it in the JOIN, meaning after ON.

You need to show us what rows need to be returned and why your current query isn't working.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

the desired result set is the third block of data above. My current query isn't working because its returning 720 rows of data where its matching up things that aren't suppose to be. I'm only expecting 120 rows

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-22 : 15:11:08

Could you trim your post down to just a few rows? I don't have the time to go through all of that data. We typically only need 5-10 rows.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-22 : 15:12:02

Also show us a few rows in your result set that shouldn't be there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-22 : 15:16:35

I got it!!

select t.hotel, t.roomtype, s.checkindate, t.roomrate, t.executiontime
from tester as t inner join morganwedcheckins as s on t.executiontime = s.executiontime
order by t.hotel, t.roomtype, s.checkindate, t.roomrate, t.executiontime

is what I needed!! Thanks Tara!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources