Page 1 of 1

Sub Queries

Posted: Wed Feb 13, 2008 3:24 pm
by GeXus
Is it possible to do something like this...

Code: Select all

 
SELECT id, name, count as (SELECT count(*) from tableB where id = '5') from table
where id = '5'
 

Re: Sub Queries

Posted: Wed Feb 13, 2008 3:33 pm
by Zoxive
You want to have value of count set its name as the result of the subquery?

Normally its the other way around.

Code: Select all

SELECT id, (SELECT count(*) FROM config) AS test FROM users;

Re: Sub Queries

Posted: Wed Feb 13, 2008 3:41 pm
by califdon
GeXus wrote:Is it possible to do something like this...

Code: Select all

 
SELECT id, name, count as (SELECT count(*) from tableB where id = '5') from table
where id = '5'
 
Almost. The As means the name you want it to be called, so:

Code: Select all

SELECT id, name, (SELECT COUNT(*) FROM tableB WHERE id = '5') as mycount 
FROM table WHERE id = '5'
If that's what you really want.

I think the more usual way to do that would be:

Code: Select all

SELECT id, name, COUNT(b.id) AS mycount 
FROM table AS a, tableB As b
WHERE a.id='5' AND b.id='5' 
GROUP BY b.id