Geek Answers Handbook

Multiple column selection in mysql 5 (5.1.42)

This problem seems like a simple problem, but I can't get it to work in one select or nest . Get authors and (if any) advisers (article) in one line.

I want to explain the problem, here are two data tables (pseudo)

papers (id, title, c_year)
persons (id, firstname, lastname)

plus a link table with one additional attribute (pseudo):

paper_person_roles(
  paper_id
  person_id
  act_role ENUM ('AUTHOR', 'ADVISER')
)

This is basically a list of written documents (table: documents) and a list of staff and / or students (table: persons)

In the article I have (1, N) authors.
The article may have consultants (0, N).
A person can be in the role of "AUTHOR" or "ADVISER" (but not at the same time).

Ultimately, the application displays the rows of the table containing the following entries:

TH: || Paper_ID | Author (s) | Title | Adviser (s) |
TD: || 21334 | John Doe, Jeff Tucker | Why the moon looks yellow | Brown, Rayleigh |
...

My first approach was this:
select / extract the full list of articles in the application, for example.

SELECT 
   q.id, q.title
FROM 
   papers AS q
ORDER BY 
   q.c_year
and save the query results in an array (in the application). After this step, loop over the array of information returned and extract the authors and consultants (if any), using the prepared statement (? Is a sheet of paper) from the link table as:
APPLICATION_LOOP(paper_ids in array)
  SELECT 
      p.lastname, p.firstname, r.act_role 
  FROM 
      persons AS p, paper_person_roles AS r
   WHERE 
      p.id=r.person_id AND r.paper_id = ?
   # The application does further processing from here (pseudo):
   foreach record from resulting records
     if  record.act_role eq 'AUTHOR' then join to author_column
     if  record.act_role eq 'ADVISER' then join to avdiser_column
   end
   print id, author_column, title, adviser_column
APPLICATION_LOOP
It still works and gives the desired result. Will it make sense to return the calculation to the database?

I am not very versed in non-trivial SQL and cannot find a solution with one (combined or nested) call choice. I'm convicted. eg

SELECT
    q.title 
    (CONCAT_WS(' ',
     (SELECT p.firstname, p.lastname AS aunames
      FROM persons AS p, paper_person_roles AS r
      WHERE q.id=r.paper_id AND r.act_role='AUTHOR')
     )
    ) AS aulist
FROM 
    papers AS q, persons AS p, paper_person_roles AS r
in several ways, but no luck ...

Maybe there is a chance?

r.b.

+5
2

, , .

/ .

Select
  p.id,
  p.title,
  p_aut.aut_name,
  p_adv.adv_name
From papers p
Left Join (
  Select pp_aut.paper_id,
         Group_Concat(Concat(p_aut.firstname, ' ', p_aut.lastname)) aut_name
  From paper_person_roles pp_aut
  Join persons p_aut On (p_aut.id = pp_aut.person_id)
  Where pp_aut.act_role='AUTHOR'
  Group By pp_aut.paper_id
) p_aut On ( p_aut.paper_id = p.id )
Left Join (
  Select pp_adv.paper_id,
         Group_Concat(Concat(p_adv.firstname, ' ', p_adv.lastname)) adv_name
  From paper_person_roles pp_adv
  Join persons p_adv On (p_adv.id = pp_adv.person_id)
  Where pp_adv.act_role='ADVISER'
  Group By pp_adv.paper_id
) p_adv On ( p_adv.paper_id = p.id )
Group By p.id, p.title
+2

SQL , , . , , , , , , , - , , .

- :

    SELECT q.id, q.title, p.firstName, p.lastName, r.act_role FROM papers q, persons p, 
        paper_person_roles r where r.paper_id = q.id and r.person_id = p.id

, , :

21334   |Why the moon looks yellow|John Doe   |AUTHOR
21334   |Why the moon looks yellow|Jeff Tucker|AUTHOR
21334   |Why the moon looks yellow|Brown      |ADVISER
21334   |Why the moon looks yellow|Rayleigh   |ADVISER

, .

:
    - ?
    - , ?
    - "" ?

, , , , , , , , .

+2

More articles:


All Articles