SQL SELECT from multiple tables + IN LIST

存储架构 2017-12-30

How can I get every article + name of all authors in the article (different from article to another)

Users table :
ID NAME FNAME
1  A    AA
2  B    BB
3  C    CC
4  D    DD

Articles
ID AUTHOR title
1  1,3    ty
2  4      tz

Results
A.id A.title USER1_name USER2_name
1    ty      A          C
2    tz      D          no user

Its working thanks, is there a way to make the one user column instead of two like this :

ID  TITLE NAME
1   ty    A,C
2   yy    B,C,E

because sometimes we have mote than 7 authors :/

Assuming maximum of 2 authors per article

select a.id, a.title, b.u1, b.u2
from articles a
join (
  select if(a.id=b.id,a.id,concat(a.id,',',b.id)) author, a.name u1, if(a.id=b.id,'no user',b.name) u2
  from users a
  join users b) b on a.author = b.author;

demo

Hello, buddy!

责编内容by:Hello, buddy! (源链)。感谢您的支持!

您可能感兴趣的

SQL vs NoSQL: One Is Definitely Better … and it can be explained with a simple flowchart: Click to zoom
Sql query does not work ALTER PROCEDURE . @ProductCode VARCHAR(200) , @OMCode VARCHAR(200) , @ProductName VAR...
SQL Union Problems When Using Order By Here is the query I am using: select top 1 'Return To IPACS' as name, 'http://domain:88' as link u...
Azure SQL Database – Optimizing For Ad-hoc Workloa... Azure SQL Database – Optimizing For Ad-hoc Workloads BLOB EATER Posted on 8 January ...
Web-based Database Management You've probably noticed from some of my other databasejournal.com articles that I'm a huge proponent...