[Koha] koha reports

[Michael Kuhn]

Hi Monisa

 > help needed
 > I need a complete patron list with item.barcodes (items still with 
that patron). this report should also include the patrons which don't 
have any item with them.
 > what i made isSELECT 
borrowernumber,borrowers.cardnumber,borrowers.surname,borrowers.firstname,borrowers.address,borrowers.address2,borrowers.branchcode,borrowers.categorycode,borrowers.dateenrolled,borrowers.dateexpiry,borrowers.debarredcomment,borrowers.borrowernotes,items.barcodeFROM 
borrowers
 > LEFT JOIN items
 > but there is a syntax error, i don't know where?Monisa Aslam

There is no direct connection between tables "borrowers" and "items", 
but table "issues" contains both borrowernumber and itemnumber, so you 
will have to start with something like this:

SELECT borrowers.borrowernumber, items.itemnumber, items.barcode
FROM borrowers
LEFT JOIN issues ON borrowers.borrowernumber=issues.borrowernumber
LEFT JOIN items ON issues.itemnumber=items.itemnumber
ORDER BY borrowers.borrowernumber, items.itemnumber

It will give you a list like this:

+----------------+------------+----------+
| borrowernumber | itemnumber | barcode |
+----------------+------------+----------+
...
| 2122 | NULL | NULL |
| 2123 | NULL | NULL |
| 2124 | 1163 | 74042220 |
| 2124 | 5839 | 74112730 |
| 2124 | 14127 | 00000826 |
| 2124 | 14156 | FERN0057 |
| 2124 | 14211 | FERN0060 |
| 2125 | NULL | NULL |
...

Also I advise to learn about the SQL syntax of JOIN since it doesn't 
work by just putting the name of a table after JOIN. For example here: 
http://www.w3schools.com/sql/sql_join.asp

Hope this helps.

Best wishes: Michael
-- 
Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis
Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz
T 0041 (0)61 261 55 61 · E mik at adminkuhn.ch · W www.adminkuhn.ch