mysql join query

Question

I have two tables that I need to join. These are:

• art

  id | art
  --------
  1  | A
  2  | B
  3  | C
  4  | D
  5  | E
  6  | F
  7  | G
  8  | H
  9  | I
  

  and

• Sess

  artid | sessid
  --------------
  1     | 1
  2     | 1
  3     | 1
  4     | 1
  1     | 2
  4     | 2
  5     | 2
  6     | 2
  1     | 3
  2     | 3
  7     | 3
  4     | 3
  

where Sess.artid is a foregin key to art.id.

From the tables above we can see that there are 3 sessions: A,B,C,D, A,D,E,F and A,B,G,D. I want to get a ranking of the arts that occur along with art A. Something like:

D=3
B=2

How could I form such a query in mysql or postgres?

Answer 1

Something like this, perhaps:

select art,count(*)
from sessid
  left join art on art.id=artid
where sessid in (select sessid from sess where artid=1)
group by artid;

?

Answer 2

You need to join twice the session table to get the article sharing the same session. Then join one time with article for the filter clause, and another time to get the name of the other article in the other session.

SELECT aSameSession.art, count(*)
FROM art a
    INNER JOIN Sess s
        ON a.id = s.artid
    INNER JOIN Sess sSameArticle
            ON sSameArticle.sessid = s.sessid
    INNER JOIN art aSameSession
        ON sSameArticle.artid = aSameSession.id
WHERE A.art = 'A'
AND aSameSession.art <> 'A'
GROUP BY aSameSession.art

Output :

B   2
C   1
D   3
E   1
F   1
G   1

This version could be a little difficult to understand, so here a version just with the ID of the article, which is much more simple :

SELECT sSameArticle.artid, count(*)
FROM Sess s
    INNER JOIN Sess sSameArticle
        ON sSameArticle.sessid = s.sessid
WHERE s.artid = 1
AND sSameArticle.artid != 1
GROUP BY sSameArticle.artid

Output :

2   2
3   1
4   3
5   1
6   1
7   1

Adding the name of the article is just cosmetic.

Answer 3

SELECT a.art, count(*) as ranking
FROM art a, sess s
WHERE a.id = s.artid
group by a.art
order by count(*) DESC;

For a statement in ANSI-92 syntax have a look at Konerak's answer.

Answer 4

Example of table structure and join queries on PostgreSQL

CREATE TABLE arts (
    arts_id serial PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE sessions (
    sessions_id integer NOT NULL,
    arts_id integer NOT NULL REFERENCES arts
);


SELECT arts.name, count(sessions_id)
FROM arts
JOIN sessions USING (arts_id)
GROUP BY arts.name
ORDER BY count(sessions_id) DESC;