asked on

so only one query will return select * from orders

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

EvilPostIt

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

chedlin

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'

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chedlin

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).

rgb192

ASKER

thanks