How Can I Grab The "COUNT" From A Correlated Subquery?

I've got a query that looks like..

SELECT id_b FROM id_table
WHERE id_a = ?
AND (
       SELECT COUNT(*) AS rowCount FROM other_table
       WHERE id = id_b
     )  > 0;

How would I retrieve the value rowCount?

EDIT: This is a MYSQL DB

Answers


Select A..., Z.RowCount
From id_table As A
    Cross Join  (
                Select Count(*) As RowCount
                From other_table
                Where id_b = ?
                ) As Z
Where A.id_a = ?
    And Z.RowCount > 0

Edit

Given your edit, I'm guessing you are trying achieve something akin to:

Select A..., Z.RowCount
From id_table As A
    Join    (
            Select id_b, Count(*) As RowCount
            From other_table
            Group By id_b
            ) As Z
        On Z.id_b = A.id_b
Where A.id_a = ?
    And Z.Cnt > 0

You could try:

SELECT (
       SELECT COUNT(*) AS rowCount FROM other_table
       WHERE id_b = ?
     ) AS rowCount, id_b FROM id_table
WHERE id_a = ?
AND (
       SELECT COUNT(*) AS rowCount FROM other_table
       WHERE id_b = ?
     )  > 0;

That would work in SQL Server


Need Your Help

How to pass dynamic parameter value in a link using Struts taglib?

java jsp struts struts-1 taglib

I'm trying to use Struts framework in my project. I want to use the html:button to send parameter in the link but I don't understand how to make it.

Starting Rails with heroku-buildpack-multi

ruby-on-rails heroku

I'm trying to use the subject software as part of loading tesseract on Heroku, but Rails is not starting up. I've taken out the non-ruby stuff so that the .buildpacks file just has the following li...