How to do a double JOIN in SQL?

Question

How to do a double JOIN in SQL?

Navigation

#1 by (0 votes)

0

I want to know stories times a subscriber_id liked a hashtag that are about ads that we call eclipse_id .

I have two tables in my database:

Swipe that gives me if a subscriber liked an advertisement:

A subscriber liked an advertisement when state = {2,3,5,6,8,9}

Here is the eclipse_hastag database with the correspondence between the advertisements and the hashtag with which they are associated.

Here is the query to know if a subscriber_id liked a hashtag through an advertisement:

-- matrice utilisateur, hastag
SELECT COUNT (swipe.state),eclipse_hashtag.hashtag_id,swipe.subscriber_id  FROM swipe
  INNER JOIN eclipse_hashtag ON eclipse_hashtag.eclipse_id = swipe.eclipse_id
  WHERE swipe.state= 3 OR swipe.state = 6 or swipe.state=9
    GROUP BY eclipse_hashtag.hashtag_id,swipe.subscriber_id
      ORDER BY eclipse_hashtag.hashtag_id DESC;

I think the solution was a double JOIN peron I'm not sure and I do not know how to do them and the next one is my first:

-- matriz usario, hashtag
SELECT COUNT(swipe.eclipse_id), subscriber_hashtag.hashtag_id,subscriber_hashtag.subscriber_id  FROM subscriber_hashtag
  -- join para que las publicidades/eclipses que gusta un usarios estan vinculadas con las de la tabla de correspondencia con los hashtag
  INNER JOIN eclipse_hashtag ON eclipse_hashtag.hashtag_id = subscriber_hashtag.hashtag_id
  -- join para que los usarios  estan vinculadas con los de la tabla de correspondencia con los hashtag
  LEFT OUTER JOIN swipe ON subscriber_hashtag.subscriber_id = swipe.subscriber_id
  -- recobremos los "me gusta"
  WHERE swipe.state= 3 OR swipe.state = 6 or swipe.state=9
    GROUP BY subscriber_hashtag.hashtag_id,subscriber_hashtag.subscriber_id
      ORDER BY subscriber_hashtag.hashtag_id DESC;

Here is the output, but it seems a bit high and I do not know why the subscriber_id is not ordered.

sql

asked by ThePassenger 06.06.2017 в 16:59

source

1 answer

Doubt with the algorithm of the hannoi tower according to wikipedia Log in PHP7

score 0 · Answer 1

Yes, [swipe] is the table where you have the relationship between suscriber_id and eclipse_id, [eclipse_hastag] is the relaton between eclipse_id and hashtag_id, and what you need is how many times a suscriber_id a liked a hashtag_id your query would be:

SELECT a.suscriber_id, b.hashtag_id, COUNT(*) as 'Tiempos'
FROM swipe as a
INNER JOIN eclipse_hastag
ON (a.eclipse_id = b.eclipse_id)
GROUP BY a.suscriber_id, b.hashtag_id
ORDER BY Tiempos DESC

A council, you can use aliases in your code to improve reading.

Greetings, I hope it serves you.