Solved

so only one query will return select * from orders

Posted on 2011-03-21
6
256 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

735 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