in one SELECT command - 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

in one SELECT command

Author

Topic

mike75
Starting Member

3 Posts

Posted - 2012-02-29 : 05:17:22

I have a table for example "Maintenance". In this table I have column "Devices". Normaly this column takes A, B or C value, but sometimes can appear D value. Can I prepare SELECT command in this way, which select rows with A value but only in situation when there aren't any rows with D value. If there are rows with D value then select only rows with D?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-29 : 07:17:14

Would something like this work for you?

SELECT * FROM YourTable y1
WHERE
(
	EXISTS 
	(	SELECT * FROM YourTable y2
	 	WHERE y2.Devices = 'D'
	)
	AND y1.Devices IN ( 'D','B','C')
)
OR
(
	NOT EXISTS 
	(	SELECT * FROM YourTable y2
	 	WHERE y2.Devices = 'D'
	)
	AND y1.Devices IN ( 'A','B','C')
);

mike75
Starting Member

3 Posts

Posted - 2012-02-29 : 07:55:46

quote:

Originally posted by sunitabeck

Would something like this work for you?

SELECT * FROM YourTable y1
WHERE
(
	EXISTS 
	(	SELECT * FROM YourTable y2
	 	WHERE y2.Devices = 'D'
	)
	AND y1.Devices IN ( 'D','B','C')
)
OR
(
	NOT EXISTS 
	(	SELECT * FROM YourTable y2
	 	WHERE y2.Devices = 'D'
	)
	AND y1.Devices IN ( 'A','B','C')
);

I didn't know that this querys construction is possible in SQL. I'm not familiar with this language. I have to first analize this piece of code before answering.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 11:52:23

quote:
Originally posted by mike75

quote:
Originally posted by sunitabeck

Would something like this work for you?
SELECT * FROM YourTable y1
WHERE
(
EXISTS
( SELECT * FROM YourTable y2
WHERE y2.Devices = 'D'
)
AND y1.Devices IN ( 'D','B','C')
)
OR
(
NOT EXISTS
( SELECT * FROM YourTable y2
WHERE y2.Devices = 'D'
)
AND y1.Devices IN ( 'A','B','C')
);

I didn't know that this querys construction is possible in SQL. I'm not familiar with this language. I have to first analize this piece of code before answering.

sounds like just this to me


SELECT TOP 1 WITH TIES *
FROM Table
WHERE Devices IN ('A','D')
ORDER BY Devices DESC

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 11:54:24

Sunita...what to look that over again?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-29 : 17:26:02

quote:
SELECT TOP 1 WITH TIES *
FROM Table
WHERE Devices IN ('A','D')
ORDER BY Devices DESC

I thought of that, but did not post it just to see if Visakh would figure out that it can be done that way. I really did, honest!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 22:27:26

quote:
Originally posted by sunitabeck

quote:
SELECT TOP 1 WITH TIES *
FROM Table
WHERE Devices IN ('A','D')
ORDER BY Devices DESC
I thought of that, but did not post it just to see if Visakh would figure out that it can be done that way. I really did, honest!!

Glad that I was able to meet your expectation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mike75
Starting Member

3 Posts

Posted - 2012-03-05 : 05:26:37

I found the solution to my problem by use LIKE CASE function. I didnt know that SQL language can offer functionality like SUBQUERY (I have never used SQL before). Thanks for help.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-05 : 11:44:30

care to share what you did? I'm still lost on the original question

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources