Solved

so only one query will return select * from orders

Posted on 2011-03-21
6
266 Views
Last Modified: 2012-05-11
select * from orders where (select top 1(name) from users = 'System')

 Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.



the two tables are not joined and have nothing in common

i want the first table to return results only if the second condition is true

select * from orders where (select top 1(name) from users = 'System')
select * from orders where (select top 1(name) from users != 'System')

so only one query will return select * from orders

0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35182442
You are not telling the first query what to search for. Try something like below.

select * from orders where name in (select top 1(name) from users = 'System')

Open in new window

0
 
LVL 2

Expert Comment

by:chedlin
ID: 35182469
I'm sure there are clearer ways to do this, but you can fix these queries by moving your )

select * from orders where (select top 1(name) from users = 'System')
select * from orders where (select top 1(name) from users != 'System')

become

select * from orders where (select top 1(name) from users) = 'System'
select * from orders where (select top 1(name) from users) != 'System'

0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 250 total points
ID: 35182511
have you tried:
select * from orders where yourcolumnnamegoeshere  = (select top 1 name from users where yourcolumnnamegoeshere = 'System')
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 250 total points
ID: 35183813
try like this.
SELECT * 
  FROM orders 
 WHERE 1 = (SELECT TOP 1 1 
              FROM users 
             WHERE name = 'System')

Open in new window

0
 
LVL 2

Expert Comment

by:chedlin
ID: 35184929
If you don't need it to return an empty result set I would move the logic into an if statement.   It would be much clearer.

if ((SELECT TOP 1 name from users) = 'System')
   select * from orders

Not having an order by on the users could cause some unexpected behavior.  The order is never guaranteed this way and you could get a nasty surprise if the clustered index gets changed (not that this is the only way there could be issues).
0
 

Author Closing Comment

by:rgb192
ID: 35191930
thanks
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question