Help with a select statement - SQL Server Forums

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

Help with a select statement

Author

Topic

kazrath
Starting Member

3 Posts

Posted - 2007-10-05 : 19:32:08

I have three columns within one table I am trying to use for comparison.

sender_name recipient_name message_date

What I am attempting to do is create a query that allows me to show all communications between the sender_name & recipient_name between a date range.

What I was able to do was the following:

SELECT * FROM messages WHERE message_date BETWEEN '8/20/2006 0:00:00' and '8/20/2006 23:59:59' and sender_name = 'testuser1'

This works and only returns testuser1 for the entire day of 8/20/2006. However I need it to return "only" the items where 'testuser1' the sender sent a message to 'testuser2' the recipient.

How would I make this query work?

Thanks ahead of time.

kazrath
Starting Member

3 Posts

Posted - 2007-10-05 : 19:33:20

to add to this:

Everytime I try to add any other statement at the end of my existing working one it begins to fail.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2007-10-05 : 19:47:28

Allright, maybe I'm missing something here but don't you just need to add testuser2 in the where??

SELECT * 
FROM messages 
WHERE message_date BETWEEN '8/20/2006 0:00:00' and '8/20/2006 23:59:59' 
  and sender_name = 'testuser1'
  and recipient_name  = 'testuser2'

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

kazrath
Starting Member

3 Posts

Posted - 2007-10-05 : 20:01:41

Yeah I was trying that and it was failing. I plugged in what you wrote and it worked great. I am guessin I typo'ed and my stinging eyes did not see it.

Just starting trying to figure out how to write queries today.

Kristen
Test

22859 Posts

Posted - 2007-10-06 : 04:14:16

"Just starting trying to figure out how to write queries today."

Couple of suggestions then, if I may:

Change

WHERE message_date BETWEEN '8/20/2006 0:00:00' and '8/20/2006 23:59:59'

to


WHERE     message_date >= '8/20/2006 0:00:00' 
      and message_date <  '8/21/2006 0:00:00'

and if you are going to use "string date constants" you should use

YYYYMMDD hh:mm:ss

format. Note: No hyphens. This format is unambiguous to SQL Server, any 99/99/99 format is ambiguous and the way it is interpreted will depend on a number of things, and you may not get what you are expecting!

The time part is optional.

So you could do this:


WHERE     message_date >= '20060820'
      and message_date <  '20060821'

Secondly, don't use SELECT * in your queries. Put the list of column names in full.

If your application needs only the columns Col1, Col2 and Col3 then put:

SELECT Col1, Col2, Col3

couple of reasons for this. Firstly, SQL Server doesn't have to work out what columns are available (i.e. convert "*" to the list of columns). This is quicker, and means that SQL Server can more often cache the query.

Secondly, and more important, SELECT * will pass ALL the columns to your application. If you add several TEXT columns in the future they will get passed too, and your application won't be using them because it was never built to handle them. So you will be passing loads of bytes that are unused, which will use up bandwidth and slow everything down.

(One exception: Use "SELECT *" when you use the EXISTS keyword - e.g.

WHERE EXISTS (SELECT * FROM MyOtherTable WERE ...)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources