Select columns AS (SELECT...)

Databases
1

Hi. I have a query that works:


SELECT node.nid AS nid, (
SELECT node_data_field_image.field_image_fid AS node_data_field_image_field_image_fid
FROM content_type_image node_data_field_image
WHERE node_data_field_image.vid = node.vid
) AS node_data_field_image_field_image_fid, 
node.type AS node_type, 
node.vid AS node_vid, 
node.created AS node_created
FROM node node
WHERE (node.status <>0)
AND (node.type IN ('image', 'video'))
ORDER BY node_created DESC
LIMIT 0 , 30

How can I return more than 1 column from inner select??
I need this query to be in this form, dont ask me why Im making this.

2

Im really impressed, googling this question gave me this thread less than 5 minutes elapsed after I post it)

3

you can’t

and since you say you “need this query to be in this form” then there’s no point in offering an alternative, is there

:slight_smile:

4

If your pulling data from two tables, try using the JOIN syntax

5

I can’t use join, I used several Select statements for each column)

6

??? Explain why you can’t use a join

7

Because I have to rewrite existing query, i dont control all the process.

If I use join for this query the performance will be lower, since join will be executed before select.

I can use sub select to acheive this, but I can’t do this since I can’t control LIMIT statement, and the limit statmement for the outer select is something like 175, 15. And if use subselect it should be simply LIMIT 15 but thats not possible for me.

8

which means your will be twice as slow, with two subselects in the outer select

and that part about LIMIT is wrong, it looks like you have a many-to-one relationship so pagination in the join (outer) query is still possible

i’m just sayin

:slight_smile:

9

I need this query to be in this form, dont ask me why Im making this.

do you mean by this, that you need the resultset from the query to be such that it matches/integrates with post-processing code? if so - and I know I am breaching your request not to ask… - how can you expect a response here to give you the best query?

Much better to get the correct resultset and then, to post-process’.

I’m not getting at you :slight_smile: it’s difficult sometimes to ask the ‘best’ question when you are trying to fit in with existing stuff.

10

Mikle, can you do a “SHOW CREATE TABLE” for the table or tables involved?