CODEWITHSUNDEEP
sqlselect
goldisfine
goldisfine

Reputation: 4850

Understanding Aliasing and Tables, SQL

I'm trying to an output table with each row representing posts in a thread and the columns subdividing the thread into question, answers, and comments. I have the question and answer part down, now I need to add the comment section.

I'm trying to understand a solution I was given in order to play around with the code and add a third column for commenters. The pasted code creates two columns from one column of students' IDs. The first column is all the students who posted a question and the second is all the students who posted an answer in a given thread.

Table queried from:

thread_id    student_usrnm     post_type
1            iron_man          question
1            orient            answer
1            cyclops           comment
2            green_lantern     question
2            iron_man          answer
...          ....              .....

Output:

questioners     answerers      commenters
iron_man        orient         cyclops
green_lantern   iron_man

Here is the code that works and generates questioners and answerers:

 SELECT s1.author_id AS questioner,
(SELECT group_concat(DISTINCT author_id SEPARATOR " ") FROM students s2 WHERE s2.post_type = 'answer' AND   s2.thread_id = s1.thread_id)  AS answerers  
 FROM students s1
 WHERE s1.post_type = 'question';

Questions:

  1. What is s1 and what is s2 and how do these work? Are they temporary tables or something?

  2. How can I add a third column for commentators? This is my pathetic attempt to generate a third column:

    SELECT s1.author_id AS questioner,
  (SELECT group_concat(DISTINCT author_id SEPARATOR " ") 
  FROM students s2 WHERE s2.post_type = 'answer' AND s2.thread_id = s1.thread_id) AS answerers, 
  (SELECT group_concat(DISTINCT author_id SEPARATOR " ") FROM students s3 WHERE s3.post_type = 'comment' and s3.thread_id = s1.thread_id) AS commenters,    
  FROM students s1
  WHERE s1.post_type = 'question';

Upvotes: 0

Views: 1432

Answers (1)

ChrisCamp
ChrisCamp

Reputation: 682

Aliasing allows you to rename both column and table names to anything of your choosing. s1 and s2 both reference the same table, "students". Here's a link that describes how to use aliasing SQL-Alias (w3schools). See the "Alias Example for Tables"

Your friend used a subquery and selected from the same student table in order to get at people who answered the questions. Your strategy for adding commentors is valid. I created a SQL Fiddle to demonstrate that this works, play around with it to further customize your results.

Upvotes: 1

Related Questions