0

I have a table representing a Ternary relation between Offers, Profiles, and Skills.

Any given Offer can have more than one profile, and more than 1 skill associated.

Something like this:

ternary_relationship table

id_Offer    -   id_Profile  -   id_Skill
1           -   1           -   1
1           -   1           -   2
1           -   1           -   3
1           -   2           -   1
2           -   1           -   1
2           -   1           -   2
2           -   1           -   3
2           -   2           -   1

Offer table

Offer   -   business_name
1       -   business-1
1       -   business-1
1       -   business-1
1       -   business-1
2       -   business-2 
2       -   business-2 
2       -   business-2 
2       -   business-2

I want to make a query filtering by a profile and only count Offer once, no matter how many skills it has associated.

I was doing something like the following query:

SELECT business_name, COUNT(*)
FROM Offer INNER JOIN
     ternary_relationship
     ON Offer.id_Offer = ternary_relationship.id_Offer AND
        id_Profile =  '1'
 GROUP BY business_name
 ORDER BY COUNT(*) DESC;

I have seen several possible solutions but I am not being able to make anyone work for my case. Neither when I group by for both name an id_Offer nor when I only filter by id_Offer it works, either. I always get duplicated entries somewhere.

monkey intern
  • 705
  • 3
  • 14
  • 34
  • 1
    You provide data from one table, with three columns. Then you have a query that is syntactically incorrect, mentioning two other tables and other columns. I'm pretty confused on what you have and what you want to do. – Gordon Linoff May 22 '18 at 15:16
  • I will edit it to make it more clear @GordonLinoff – monkey intern May 22 '18 at 15:17
  • 1
    And for that example input, what output do you want? And why does the offer table have the exact same data repeated 4 times? – MatBailie May 22 '18 at 15:22
  • what about subselect ? – Christian Felix May 22 '18 at 15:24
  • @MatBailie I want an output saying how many offers are for a certain profile: For profile 1, there are 2 offers. No matter how many skills there are. The offer table is a quickly made example, I can edit more random data, I thought I had put different ID's and what not. – monkey intern May 22 '18 at 15:28
  • @monkeyintern - Add the ***exact*** results that you want to your question. *(Your attempted query **implies** a count of offers per business, your comment is a count of offers per profile. Until you give categorical examples this is going to remain ambiguous.)* – MatBailie May 22 '18 at 15:29
  • I do not have much experience with SQL and specially not with subselects, I tried __ SELECT business_name, COUNT(*) FROM Offer INNER JOIN (SELECT DISTINCT id_oferta FROM oferta_skill_perfil) as subq ON Offer.id_Offer = subq.id_Offer AND id_Profile = '1' GROUP BY business_name ORDER BY COUNT(*) DESC; __ But it fails with "Unknown column 'id_perfil' in 'where clause'" Error – monkey intern May 22 '18 at 15:30
  • Why does `offer` have duplicates? – Gordon Linoff May 22 '18 at 15:35
  • Where's your primary key? – Strawberry May 22 '18 at 16:04
  • This is not the correct way to create many to many relationships between 3 tables, You must have one table for the relationship between Offer and Profile and another table to create a relationship between Profile and Skill. – QMaster May 22 '18 at 18:46
  • Please edit your question and add result you expect to have. – QMaster May 22 '18 at 18:51

1 Answers1

0

You can do the JOINS and for once count use DISTINCT

select o.business_name, count(distinct tr.id_Offer) counts
from Offer o inner join 
     ternary_relationship tr on 
     o.Offer = tr.id_Offer 
where tr.id_Profile = ?
group by o.business_name
order by 2 desc;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • You succeed to understand the question and this is the correct way to get result @monkey need, But Ternary table is not a wise solution I think as I mentioned below the question. – QMaster May 22 '18 at 18:49
  • I am afraid I do not get this type of query to work. What is the " where tr.id_Profile = ? " doing? How is it called in sql when you give an '?' to a where clause? I get the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? ORDER BY COUNT(*) DESC' at line 1 – monkey intern May 24 '18 at 08:44