0

I have the following query

select count(t1.guid)
from table t1
where t1.id=X;

X is a result-set from this query

select ID 
from table t2 
where t2.flags=65537;

The above query returns 84 results, all of INT datatype.

id is primary key in t2 table, and foreign key in t1 table;

guid is primary key in t1 table, and doesn't exist anywhere else.

Object O1 has a unique identifier among the table that declares all objects and their properties (t2) GUID in table t1 assigns unique identification to every instance of object O1 called by upper layers. I want to see the number of duplicates every object that fulfills conditions in the second query.

I suppose I should go about declaring a variable and a function that uses said variable but got no clue where to start or how to go about it.

I solved the problem once with hand-hacking 84 times, but looking for a more elegant and more adaptive solution to this;

Nikola Pavlovic
  • 91
  • 10
  • Can you provide sample data and what is the data you are expecting out of them? So that we can understand better. What is Object O1, what do you mean by their properties, GUID. What do you mean by upper layers etc.. ? – Arihant Sep 12 '18 at 19:00
  • the second query defines "X" so 84 times instead of X, i want to substitue for something which will in turn change to valid integer subquery won't do it because it can't carry more than one row value ( mysql said it so ) – Nikola Pavlovic Sep 12 '18 at 19:06
  • can you provide table schema and some sample data in the tables ? – Arihant Sep 12 '18 at 19:12
  • If sub-query were allowed to return more than one row, my solution to hand-hacking would be select count(t1.guid) from t1 where t1.id=(select t2.id from t2 where t.flags=65537); sub-query returns 84 results, and I want to use these results in the main query to find duplicates. t1.guid= PK in t1 t1.id=FK to t2.id t2.id=PK in t2 – Nikola Pavlovic Sep 13 '18 at 07:51

1 Answers1

0

After a whole day spent, figured it out

Simply link the two posted queries together, but change the "=" operator to "in"

select count(t1.guid)
from table t1
where t1.id in
(select t2.ID 
from table t2 
where t2.flags=65537);

hand-hacking session avoided!

Nikola Pavlovic
  • 91
  • 10