So the first table only has 500 records by end up with 150000
And Im inner joining everything, dont know what to do
Inner join may not be the join type to use for what you are trying to accomplish.
Are you familiar with the other types of SQL joins? We have an article on SQL JOINS here.
Do any of your tables have a one-to-many or many-to-many relationship? If so, that would be multiplying your results.
Yes they have MANY TO MANY ONE TO MANY.
I tried using subqueries but there are many tables that I have to join and after using the first subquery that helps me to not get more multiplied records the nest subquery starts to multiply the records again.
Select count(*)
FROM "public"."checklist_checklistsectionitem" INNER JOIN "public"."checklist_noncompliance" "Checklist Noncompliance" ON "public"."checklist_checklistsectionitem"."id" = "Checklist Noncompliance"."item_id"
INNER JOIN "public"."checklist_checklistitem" "Checklist Checklistitem - Check List Item" ON "public"."checklist_checklistsectionitem"."check_list_item_id" = "Checklist Checklistitem - Check List Item"."id"
INNER JOIN (SELECT DISTINCT "checklistitem_id", "operation_id" --, others columns...
FROM "public"."checklist_checklistitem_operations"
) as "coltab"
ON "coltab"."checklistitem_id" = "Checklist Checklistitem - Check List Item"."id"
INNER JOIN (SELECT DISTINCT "public"."operations_operation"."id" --, others columns...
FROM "public"."operations_operation"
) as "coltab_2"
ON "coltab_2"."id" = "coltab"."operation_id"
THAT LAST ONE FAILS
Easiest way to write a SQL query is anywhere other than Metabase! Use whatever UI your database provides, then paste the query into Metabase. Much easier to see what's going on.
That inner join with the nested query is nasty - hard to write, hard to maintain, hard to debug. Looking into using Common Table Expressions (CTE), you'll see them starting with a 'WITH' command. You'll never return to nested queries again.