I have a table that contains a master list of items and an associated table that contains a unique text string only on specific values (determined by the application that inserts the data).

Example: Master Table (Assets)

Asset_ID    IP Address           Operating System    Host Name
1           123.456.789.125      Windows             BobsComputer
2           987.654.321.126      Windows             TomsComputer
3           456.123.789.127      Windows             JennysComputer

The associated table Unique_ID looks like this, joined on the Asset_ID

Special_ID  Asset_ID    Unique_ID
1           1           xhsieriyfh0308487
2           3           098uindifnoei8384

What I am trying to get is a query that gives me a of all items both IN Unique_ID and NOT IN Unique_ID Essentially the output I'm looking for is:

With_Unique_ID      Without_Unique_ID
2                   1

I know it has something to do with making subqueries but I can't seem to find the right syntax/command order to make it work. It's if that makes a difference.

  • What does it mean to have a column "in unique_id"? define "item" (guessing asset in master table)? does "in unique_id" mean connected by way of asset? can you delete the columns you're not using, and perhaps give reasonable names (table is called "_id")? No reason to make things more complex than need be. Mar 26 '18 at 22:20
  • The Assets table has all the items in inventory. The Unique_ID field only contains a record associated with an asset if a specific flag (determined by the logic in the application) is true, so not every Asset has a record in the Unique_ID table.
    – Sean
    Mar 28 '18 at 14:53

Something like this may be:

select count(u.asset_id) as with_unique_id,
       count(*) - count(u.asset_id) as without_unique_id
from assets a left join the_other_table u on a.asset_id = u.asset_id

(not tested)


You can use conditional aggregation with an outer join

select count(*) filter (where at.asset_id is not null) with_unique_id, 
       count(*) filter (where at.asset_id is null) as without_unique_id
from assets a
  left join associated_table at on at.asset_id = a.asset_id;
  • The issue with this is there isn't a null value in Unique_ID when the tables are joined, even with a left join. Honestly not sure why, even though I know there's supposed to be. But I know that if I run a test query that searches for NULL values after the join, nothing is returned.
    – Sean
    Mar 28 '18 at 14:50
  • @Sean: there will be a NULL value in asset_id due to the outer join Mar 28 '18 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.