CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
user2945772
user2945772

Reputation: 13

SQL - Select record count from column value in separate table

I'm totally stumped here, I hope I haven't overlooked an answer elsewhere.

I have two tables, asset and purchase_order.

asset

asset_key | description | purchase_order_key

purchase_order

purchase_order_key | order_number | number_of_assets

I'm trying to get a list of purchase orders, each meeting the following criteria:

For example, say the asset table has 4 rows with the same purchase_order_key (24), and 17 items with a different purchase_order_key (66). If purchase_order 24 lists 4 number_of_assets, and purchase_order 66 lists 19 number_of_assets, then the query should return purchase_order 66, but not purchase_order 24.

Upvotes: 1

Views: 1661

Answers (3)

Nathan Koop
Nathan Koop

Reputation: 25197

You need to do a COUNT on the assets joined to the PO table, and then compare them with the recorded number of assets.

SELECT PO.purchase_order_key, COUNT(A.purchase_order_key)
FROM purchase_order PO
    LEFT JOIN asset A ON PO.purchase_order_key = A.purchase_order_key
GROUP BY PO.purchase_order_key, PO.number_of_assets
HAVING COUNT(*) > PO.number_of_assets

Upvotes: 1

StyxUT
StyxUT

Reputation: 26

I think this is what you are looking for:

SELECT
    PO.*
FROM
    purchase_order PO
    INNER JOIN
        (
            SELECT 
                A.purchase_order_key,
                COUNT(A.asset_key) asset_key_count
            FROM
                asset A
            GROUP BY
                A.purchase_order_key

        ) AC on AC.purchase_order_key = PO.purchase_order_key and AC.asset_key_count < PO.number_of_assets

Upvotes: 0

Jason Goemaat
Jason Goemaat

Reputation: 29214

You can use a subquery:

SELECT *
FROM purchase_order
WHERE number_of_assets > (
    SELECT COUNT(*)
    FROM asset
    WHERE asset.purchase_order_key = purchase_order.purchase_order_key
)

Upvotes: 1

Related Questions