how to do this: - 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

SQL Server 2000 Forums

Transact-SQL (2000)

how to do this:

Author

Topic

b007
Starting Member

9 Posts

Posted - 2008-08-02 : 09:05:26

I want to select a column from an answer for a query
like this:
SELECT *
FROM (SELECT * FROM MSGS WHERE MSGS.ID = '1')

Something like this.
What is the correct syntax to write this kinds of queries?

Thanks, Gil.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-02 : 09:38:38

need the table alias name


SELECT *
FROM (SELECT * FROM MSGS WHERE MSGS.ID = '1') as a

KH
[spoiler]Time is always against us[/spoiler]

r937
Posting Yak Master

112 Posts

Posted - 2008-08-02 : 10:14:38

need to drop the outer query altogether

SELECT * FROM MSGS WHERE ID = '1'

also, is ID numeric or string?

rudy
http://r937.com/

b007
Starting Member

9 Posts

Posted - 2008-08-02 : 10:29:12

What 'as a' means here?

b007
Starting Member

9 Posts

Posted - 2008-08-02 : 10:32:46

number so I removed the ' '.
Now I wonna do this:
SELECT 3
FROM (SELECT Sunday as '1', Monday as '2',Tuesday as '3', Wednesday as '4', Thursday as '5', Friday as '6', Saturday as '7' FROM Screen_Msgs) as a

that it will show me only day number 3. and his details in the table.
But it shows me a 1 column with line data 3

what to do?
Thanks, Gil

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-08-02 : 10:48:29

why not just this then:

SELECT Wednesday FROM Screen_Msgs

elsasoft.org

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-02 : 11:30:54

quote:
Originally posted by b007

What 'as a' means here?

a is the alias name given to the derived table

KH
[spoiler]Time is always against us[/spoiler]

b007
Starting Member

9 Posts

Posted - 2008-08-02 : 11:31:02

Couse I want to select the day as today from Screen_Msgs
using DATEPART(WEEKDAY,GETDATE())
got me?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-02 : 11:34:53

quote:
Originally posted by b007

Couse I want to select the day as today from Screen_Msgs
using DATEPART(WEEKDAY,GETDATE())
got me?

Sorry. No.

Maybe it will be clearer to all if you can post the DDL for Screen_Msgs and some sample data to illustrate what you want.

Also include your expected result

KH
[spoiler]Time is always against us[/spoiler]

b007
Starting Member

9 Posts

Posted - 2008-08-02 : 11:51:23

Wha's 'as a' meaning?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 12:21:37

quote:
Originally posted by b007

Wha's 'as a' meaning?

didnt you read what khtan posted on 08/02/2008 : 11:30:54?

b007
Starting Member

9 Posts

Posted - 2008-08-02 : 12:23:18

ok. Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 12:29:46

quote:
Originally posted by b007

Someone can answer me please..

'as a' means you are naming the derived table as a.its called table alias.you need a table alias if you're using a derived table.

and this was clearly suggested by khtan before. suggest you to read all reply before asking again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 12:36:02

quote:
Originally posted by b007

ok. Thanks,

please dont edit your original post for replies. just use reply to option.

b007
Starting Member

9 Posts

Posted - 2008-08-02 : 13:19:54

I got this table:
MsgName (varchar)
MsgID int (PK)
Sunday bit (false/true)
Monday bit (false/true)
Tuesday bit (false/true)
Wednesday bit (false/true)
Thursday bit (false/true)
Friday bit (false/true)
Saturday bit (false/true)

I would like to return all the MsgID that is on today
there for I used DATEPART(WEEKDAY,GETDATE()) that return me the number of today's day.

from here I dont know what to do.
Please help me.

Thanks, Gil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 13:48:44

quote:
Originally posted by b007

I got this table:
MsgName (varchar)
MsgID int (PK)
Sunday bit (false/true)
Monday bit (false/true)
Tuesday bit (false/true)
Wednesday bit (false/true)
Thursday bit (false/true)
Friday bit (false/true)
Saturday bit (false/true)

I would like to return all the MsgID that is on today
there for I used DATEPART(WEEKDAY,GETDATE()) that return me the number of today's day.

from here I dont know what to do.
Please help me.

Thanks, Gil

SELECT MsgID
FROM Table
WHERE (DATENAME(WEEKDAY,GETDATE()) = 'Sunday' AND Sunday =1) 
OR (DATENAME(WEEKDAY,GETDATE()) = 'Monday' AND Monday =1) 
OR (DATENAME(WEEKDAY,GETDATE()) = 'Tuesday' AND Tuesday =1) 
OR (DATENAME(WEEKDAY,GETDATE()) = 'Wednesday' AND Wednesday =1) 
OR (DATENAME(WEEKDAY,GETDATE()) = 'Thursday' AND Thursday =1) 
OR (DATENAME(WEEKDAY,GETDATE()) = 'Friday' AND Friday =1) 
OR (DATENAME(WEEKDAY,GETDATE()) = 'Saturday' AND Saturday =1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources