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 [email protected] · W www.adminkuhn.ch
_______________________________________________
Koha mailing list http://koha-community.org
[email protected]
https://lists.katipo.co.nz/mailman/listinfo/koha
