Sub Queries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Sub Queries

Post 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'
 
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: Sub Queries

Post 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;
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Sub Queries

Post 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
Post Reply