3 Replies - 312 Views - Last Post: 16 January 2018 - 02:24 PM

#1 fearfulsc2   User is offline

  • D.I.C Regular

Reputation: 15
  • View blog
  • Posts: 254
  • Joined: 25-May 16

Get Count of Tables that have a row that references a table

Posted 16 January 2018 - 01:41 PM

Hey guys, I am trying to work on a functionality that returns the count of tables that have a row in that table that has a foreign key reference to another table.

For example, I have 5 tables and only 4 of them have a foreign key id that exists in the main table being referenced. Is it possible to return a count of 4?

So for example:
MainTable

Table 1: MainTableId1
Table 2: MainTableId1
Table 3: MainTableId1
Table 4: MainTableId1
Table 5: empty

Can I return a count of 4 with a query and then eventually return a count of 5 when Table 5 receives data that has a reference to it?

Thank you

Is This A Good Question/Topic? 0
  • +

Replies To: Get Count of Tables that have a row that references a table

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • Posts: 60,478
  • Joined: 12-June 08

Re: Get Count of Tables that have a row that references a table

Posted 16 January 2018 - 01:54 PM

You could simply do a 'SELECT 1' query on each table for a given key, and sum them up.

The trick would be to use 'UNION ALL'

Example:
select sum(a)
from (
select 1 as a

union all

select 1 as a
) as b

Was This Post Helpful? 0
  • +
  • -

#3 fearfulsc2   User is offline

  • D.I.C Regular

Reputation: 15
  • View blog
  • Posts: 254
  • Joined: 25-May 16

Re: Get Count of Tables that have a row that references a table

Posted 16 January 2018 - 02:05 PM

View Postmodi123_1, on 16 January 2018 - 01:54 PM, said:

You could simply do a 'SELECT 1' query on each table for a given key, and sum them up.

The trick would be to use 'UNION ALL'

Example:
select sum(a)
from (
select 1 as a

union all

select 1 as a
) as b



I just want to see if a value exists and if it does, add 1 to the count. So TABLE1 can have multiple rows with different data but all referencing the same foreign ID and it should still only increase the counter by 1 since it has at least one entry with that id.

So the recommendation would to to write a select query for each table? Or would there be a more efficient method since this case I'm working on has 20+ tables to try this on.
Was This Post Helpful? 0
  • +
  • -

#4 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2880
  • View blog
  • Posts: 11,262
  • Joined: 03-December 12

Re: Get Count of Tables that have a row that references a table

Posted 16 January 2018 - 02:24 PM

Start with this and work out from there.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1