当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - How to join tables with SQL query and take number of tied columns?

问题描述:

I'm having BookTable in database (with foregin hey LibID):

| BookID | BookName | BookPrice | LibID |

-------------------------------------------

| 1 | Book_1 | 200 | 1 |

| 2 | Book_2 | 100 | 1 |

| 3 | Book_3 | 300 | 2 |

| 4 | Book_4 | 150 | 4 |

and also LibraryTable:

| LibID | LibName | LibLocation |

-----------------------------------

| 1 | Lib_1 | Loc_1 |

| 2 | Lib_2 | Loc_2 |

| 3 | Lib_3 | Loc_3 |

| 4 | Lib_4 | Loc_4 |

I need to write SQL query that will return be the info about the library and number of books for that library:

| LibID | LibName | NumberOfBooks|

------------------------------------

| 1 | Lib_1 | 2 |

| 2 | Lib_2 | 1 |

| 3 | Lib_3 | 0 |

| 4 | Lib_4 | 1 |

It should be one SQL query, probably with nested queries or joins.. Not sure how the query should look like:

SELECT L.LibID AS LibID, L.LibName AS LibName, COUNT(B) AS NumberOfBooks

FROM LibraryTable L, BookTable B

WHERE L.LibID = B.LibID

Will that work?

网友答案:

No, this query will not work. COUNT aggregates data, so you must explicitely tell the DBMS for which group of data you want the count. In your case this is the library (you want one result record per library).

COUNT's parameter is a column, not a table, so change this to * (i.e. count records) or a certain column (e.g. LibID).

The join syntax you are using is valid, but deprecated. Use explicit joins instead. In your case an outer join would even show libraries that have no books at all, if such is possible.

select l.libid, l.libname, count(b.libid) as numberofbooks
from librarytable l
left outer join booktable b on b.libid = l.libid
group by l.libid;

You could also do all this without a join at all and get the book count in a subquery instead. Then you wouldn't have to aggregate. That's way simpler and more readable in my opinion.

select 
  l.libid, 
  l.libname, 
  (select count(*) booktable b where b.libid = l.libid) as numberofbooks
from librarytable l;
网友答案:
SELECT lt.LibID AS LibID, lt.LibName AS LibName, count(*) AS NumberOfBooks
FROM BookTable AS bt
LEFT JOIN LibraryTable AS lt ON bt.LibID = lt.LibID
GROUP BY bt.LibID
分享给朋友:
您可能感兴趣的文章:
随机阅读: