2 SELECTS in 1 Query

watchachi · #1 (**permalink**) 06-07-04, 15:19

Hi,

i'm trying to do a query that shows a value of one day if that value is different from the same value in another day. All values at the same table. Like this:

the table is like this

date | value

i tried something like this and it gives me a syntax error.

SELECT * FROM table WHERE date = '2004-06-02' and value = (SELECT value FROM table WHERE date = '2004-06-01')

Strikerz95 · #2 (**permalink**) 06-07-04, 17:06

Try putting the "from table" section at the end of the statement (as long as everything is being pulled from one table. Like this:

SELECT * WHERE date = '2004-06-02' and value = (SELECT value WHERE date = '2004-06-01') FROM table;

watchachi · #3 (**permalink**) 06-08-04, 08:27

It still give me syntax errors. i tried to subquery with other tables and it
doesn't work either.

I dont understand, the version of the mysql here is 4.1.1 and it is suposed to support this kind of query.

Pat Phelan · #4 (**permalink**) 06-08-04, 08:39

This is purely a guess, so you'll have to check it over carefully, but:

Code:

SELECT date, value
   FROM table AS a
   WHERE  '2004-06-02' = date
      AND value <> (SELECT value
         FROM table AS b
         WHERE '2004-06-01' = date);

This is a pure "shot in the dark", but it is my best guess based on your original posting.

-PatP

watchachi · #5 (**permalink**) 06-08-04, 16:01

I just saw that my mysql version is 4.0 and not 4.1 as i tought. I think the subqueries
are fine.. my DB that is outdate. And i tried to update to the 4.1 but i got errors.
thanks for the answers...

Leaden · #6 (**permalink**) 06-08-04, 16:05

install the current stable version to a different directory

copy over all the info in the /mysql/data directory

check to see if you need to chown mysql:mysql .. the new directory

delete the old directory

rename the new directory to the old one

.. poof you have upgraded ..

that's how I would do it. I am running 4.3.4 atm.

Internet Services

Web Development

Digital Marketing

Consumer Tech