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?

Cyril Gandon · Accepted Answer · 2011-06-22 15:26:43Z

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 :

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 :

Adding the name of the article is just cosmetic.

edited Jun 22, 2011 at 15:26

answered Jun 22, 2011 at 12:56

Cyril Gandon

17.1k15 gold badges84 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sfairas Over a year ago

Thanks Scorpi0. Your query seems to be on track. However, I haven't managed to get it to work as I want. I may be doing something wrong though. Should I include those inned joins nested as you wrote?

Cyril Gandon Over a year ago

What output do you have ? You don't have to write inner join indent like me.

Sfairas Over a year ago

By running the query bellow I get an empty result set: SELECT aSameSession.art, count(*) AS rank FROM articles a INNER JOIN Sess s ON a.id = s.artid INNER JOIN art aSameSession ON s.artid = aSameSession.id WHERE a.art = 'A' AND aSameSession.art != 'A' GROUP BY aSameSession.art

Sfairas Over a year ago

No result with this neither. I also get an empty result back.

Sfairas Over a year ago

Thanks a lot man! This definitely does the job for me. It also does not include a rank to itself and it's faster than ADW's query.

Andriy M · Accepted Answer · 2011-06-22 14:02:46Z

2

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;

?

edited Jun 22, 2011 at 14:02

Andriy M

78k18 gold badges100 silver badges157 bronze badges

answered Jun 22, 2011 at 10:55

ADW

4,08019 silver badges13 bronze badges

3 Comments

Fabian Barney Over a year ago

@Sfairas Accept the answer, when it is exactly what you needed!

ypercubeᵀᴹ Over a year ago

This will work in MySQL but not sure about Postgres. Scorpi0's answer is better I think.

Sfairas Over a year ago

Scorpi0's answer seems to be on the right track. However, I haven't managed to get to work as I need.

jkj · Accepted Answer · 2011-06-22 11:08:59Z

0

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;

answered Jun 22, 2011 at 11:08

jkj

2,6111 gold badge18 silver badges24 bronze badges

Comments

Fabian Barney · Accepted Answer · 2011-06-22 13:56:40Z

0

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.

edited Jun 22, 2011 at 13:56

answered Jun 22, 2011 at 10:52

Fabian Barney

14.6k6 gold badges45 silver badges63 bronze badges

2 Comments

Konerak Over a year ago

This is correct, but try teaching the ANSI-92 syntax instead of the old ANSI-89 syntax.

Fabian Barney Over a year ago

You're right. I tend to use ANSI-89 because I find it much more readable. But that's probably only because I am a creature of habit. There are good reasons for using ANSI-92 syntax...so +1 :-)

Collectives™ on Stack Overflow

mysql join query

4 Answers 4

5 Comments

3 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related