?
Solved

so only one query will return select * from orders

Posted on 2011-03-21
6
Medium Priority
?
272 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 1000 total points
ID: 35182511
have you tried:
select * from orders where yourcolumnnamegoeshere  = (select top 1 name from users where yourcolumnnamegoeshere = 'System')
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 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