SELECT subqueries

Wenggo · September 14th, 2010, 10:16 AM

SELECT
(SELECT a.value FROM custom_table a WHERE a.bug_id = i.bug_id AND a.field_id = 4),
(SELECT b.value FROM custom_table b WHERE b.bug_id = i.bug_id AND b.field_id = 5),
(SELECT c.value FROM custom_table c WHERE c.bug_id = i.bug_id AND c.field_id = 6)
FROM custom_table i
WHERE i.bug_id = 27982

I got error, with code above....helppp...can anybody correct it.

Old Pedant · September 14th, 2010, 06:18 PM

Again, what version of MySQL are you using.

And, again, why are you using a subquery in the first place???

Why not keep it simple?

Code:

SELECT value 
FROM custom_table
WHERE bug_id = 27982
AND field_id IN (4,5,6)

Hmmm...if you truly need all 3 values in a single record, then I guess you could do it as

Code:

SELECT a.value, b.value, c.value
FROM custom_table AS a, custom_table AS b, custom_table AS c
WHERE a.bug_id = 27982
AND b.bug_id = a.bug_id
AND c.bug_id = a.bug_id
AND a.field_id = 4
AND b.field_id = 5
AND c.field_id = 6

But see, still no subquery needed.

Wenggo · September 15th, 2010, 01:56 AM

Thanks for the advice, now I already got all 3 values in a single record, and all this 3 fields should related with MAX(bugnote_text_id) value.

Let say :-

a b c 1
d e f 2
g h i 3

It should come out :-

g h i 3

Wenggo · September 15th, 2010, 06:37 PM

Fuhh ! i still cannot get fields to follow with MAX(bugnote_text_id), any advice on this.

Old Pedant · September 15th, 2010, 07:51 PM

Well, the best advice I can give is to move to MySQL Version 5.1.

4.0 is really very very old, and it's way past time to move on.

Wenggo · September 15th, 2010, 09:27 PM

Thanks Old Pedant.