Sub Query

[Kal]

Hi Guys,

I have the following query:


SELECT * FROM provisions p
INNER JOIN master m ON p.m_master_id=m.m_master_id
INNER JOIN business_address ba ON p.m_master_id=ba.m_master_id
INNER JOIN clis c ON p.m_master_id=c.m_master_id
INNER JOIN division d ON d.d_division_id=m.d_division_id
INNER JOIN accounts a ON p.a_account_id=a.a_account_id
WHERE
p.m_master_id IN (SELECT c.m_master_id, COUNT(c.c_number_of_channels) AS ct FROM clis c WHERE p.m_master_id = c.m_master_id GROUP BY c.m_master_id)
AND p.p_week_number = 17
AND p.p_week_year = 2008
GROUP BY m.m_master_id


CLI Table

m_master_id
c_cli_id
c_status
c_number_of_channels

Tcli table can hold multiple records for each m_master_id. How can I return counts of different c_status and total number of channels.

I have treid the above query but no luck as of yet.

Any Help Would Be Great.

[Fumigator]

For one thing you are using the alias "c" twice-- no good. Each alias must be unique.

For another thing when you use a sub query as you are doing, you must return only one column that will then be substituted into the WHERE clause. Visualize each step the query takes: First, it runs the sub-query and its result then gets fed into the main query. Then, the main query runs using the sub-query's results. Looking at it like that, you can see that it doesn't make any sense to select both a master ID and a count.

[Kal]

I think i need to be returning more than one column in the subquery as I need the total number of channels, status, and a count for each status. So would that mean it's not possible get what i need?

[Fumigator]

That means it's not a subquery that will get you what you need. Sounds like you need to join that table with the provisions table, or use two different subqueries (one in the WHERE clause and one in your column list).

A subquery as you're using it simply can't return more than the one column that is then compared to master ID.