Sub-Select - 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

Other Forums

MS Access

Sub-Select

Author

Topic

Core
Starting Member

15 Posts

Posted - 2008-04-28 : 15:33:27

Hello all,

I'm driving crazy with this one.
How can I do sometinh like this:

Select * From tbl1 WHERE value1 IN
(Select value1 From tbl1 WHERE Bla Bla IN
(Select value1 From tbl1 WHERE Bla Bla IN))

Wicth means filter a table, filter those results, them filter those last results again.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 15:34:34

Show us a data example of what you mean.

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

Core
Starting Member

15 Posts

Posted - 2008-04-28 : 15:40:30

SELECT * FROM table WHERE (FORMAT(datainicio, "yyyy") >= '2005' AND FORMAT(datainicio, "yyyy") <= '2009')

FROM THOSE WHERE (FORMAT(datainicio, "mm") >= '01' AND FORMAT(datainicio, "mm") <= '04')

FROM THESE LAST (FORMAT(datainicio, "dd") >= '01' AND FORMAT(datainicio, "dd") <= '19')

Filter Year's -> Filter Month -> Filter the days

Ps.
Cant use BEETWEEN
I try with AND's but have some bugs

MAANY MANY THANKS

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 15:45:42

Please post sample data and not queries.

What dbms are you using? FORMAT isn't a valid T-SQL function.

Why aren't you just doing this instead:
SELECT * FROM YourTable WHERE datainicio BETWEEN '01-01-2005' AND '04-19-2005'

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

Core
Starting Member

15 Posts

Posted - 2008-04-28 : 15:51:54

Because the field type is Char not Time :S Not my fault, can't change. :(

This was the only solution i tought to resolve this situation, it just a simple quenstion: Witch are the row's between date1 and date2 ?

Data Example:

01-02-2005
03-05-2008
17-10-2005

Between 01-01-2008 12-12-2008

Result: 03-05-2008

But like i said can't use the Beetween function because it's a VarChar field.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 15:55:16

Then convert them to datetime in the query:
SELECT * FROM YourTable WHERE CONVERT(datetime, datainicio) BETWEEN '01-01-2005' AND '04-19-2005'

Next time provide the level of detail in your last post to make your problem clear. We can't read your mind.

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

Core
Starting Member

15 Posts

Posted - 2008-04-28 : 15:59:16

Sorry tkizer..

One small problem, can't use the CONVERT too.. :S
I thought it too..

Cant you see a way to make this work ? :

SELECT * FROM table WHERE (FORMAT(datainicio, "yyyy") >= '2005' AND FORMAT(datainicio, "yyyy") <= '2009')

FROM THOSE WHERE (FORMAT(datainicio, "mm") >= '01' AND FORMAT(datainicio, "mm") <= '04')

FROM THESE LAST (FORMAT(datainicio, "dd") >= '01' AND FORMAT(datainicio, "dd") <= '19')

Many Thanks

Core
Starting Member

15 Posts

Posted - 2008-04-28 : 16:01:04

With no FORMAT sure..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 16:01:52

You haven't told us which dbms you are using. You've posted on a Microsoft SQL Server site, so our answers will be T-SQL. CONVERT is valid T-SQL whereas FORMAT isn't.

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

Core
Starting Member

15 Posts

Posted - 2008-04-28 : 16:06:13

Oh sry,

Have to do it in SQL and in ACCESS , thats because i cant use some things, and that's because that was the only way i thought. Like a universial query.. It's an exercise. :P

So, can you help me ?

Thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 16:07:17

I'm moving your thread to the Access forum as I've answered it for SQL Server.

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

Core
Starting Member

15 Posts

Posted - 2008-04-28 : 17:03:54

The question remains : How-to

SELECT * FROM table WHERE (FORMAT(datainicio, "yyyy") >= '2005' AND FORMAT(datainicio, "yyyy") <= '2009')

FROM THOSE WHERE (FORMAT(datainicio, "mm") >= '01' AND FORMAT(datainicio, "mm") <= '04')

FROM THESE LAST WHERE (FORMAT(datainicio, "dd") >= '01' AND FORMAT(datainicio, "dd") <= '19')

Thank you again...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources