Gossamer Forum
Home : General : Databases and SQL :

Select * from (Select statement) ???

Quote Reply
Select * from (Select statement) ???
Hi,

I am using mysql. I want to make a select statement where I select rows from the result of another select statement. In other words I want to use the first selectstatement just as I would a table, and selcet from that in my second statement. I was thinking the following would be a good way to do that, but it does not seem to work (and probably it's not suposed to?!?) But if this is not the way to do it, could someone give me some good suggestion on how to handle this kind of need?

(Simplified version, that doesn't make sence...but the idéa should be clear?)

SELECT *
FROM (
SELECT *
FROM tablenamn WHERE a = 'A'

)

(Complex version, this is something like what I would be using if it worked...)

SELECT DISTINCT no1.filnamn AS filnamn, no1.datum AS datum
FROM (
SELECT DISTINCT f.filnamn AS filnamn, f.datum AS datum
FROM foto f, foto_fotokategori fk
WHERE ( f.filnamn = fk.foto AND fk.fotokategori = 3)
ORDER BY datum DESC , filnamn DESC

)no1, (
SELECT DISTINCT f.filnamn AS filnamn, f.datum AS datum
FROM foto f, foto_fotokategori fk
WHERE ( f.filnamn = fk.foto AND fk.fotokategori = 2)
ORDER BY datum DESC , filnamn DESC

)no2
WHERE ( no1.filnamn = no2.filnamn

)
ORDER BY no1.datum DESC , no1.filnamn DESC
LIMIT 0 , 30



Could someone give me some good suggestion on how to handle this kind of need?
Quote Reply
Re: [Netmos] Select * from (Select statement) ??? In reply to
Hi,

MySQL does not yet support subselects. You could try changing your query to do unions instead, but I'm not sure if it's possible.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Select * from (Select statement) ??? In reply to
Thanx Alex,

Saves me a lot of time knowing this! Tongue

I don't think a UNION will make it for me, but probably if I toss the data over to some php-arrays I can work around it somehow... Thanks, again for a quick and clear reply!

/ Netmos