SQL Select HELP!!!

[vinceinhiding]

I have a table called people. Each person has a name, and income. I want to produce a set of records of people who have an income higher than that of a specific person (BOB). I want to refer to BOB's income as a select statement so that if it changes later on, the statement still holds it conditions.

I tried something like this:
SELECT name, income
FROM people
WHERE income > (SELECT income FROM people WHERE name = 'BOB')

That didnt work, how do i do this????

 

[tlbroadbent]

Your query looks correct. What is your RDMS and what is the result of running the query? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[vinceinhiding]

This is the error.

ERROR 1064: You have an error in your SQL syntax near 'SELECT salary FROM people)' at line 1

I am running this on mySQL. I have just learned that mySQL does not like nested SELECT statements. But i still havent figured out a way around it.

 

[tlbroadbent]

You might try this query. It's not efficient but it works.

SELECT Distinct name, income
FROM people As a, people As b
WHERE a.income > b.income
And b.name = 'BOB'

May I suggest forum436 for MySQL questions. This is the ANSI SQL forum.
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.