quote:

---

Originally posted by wippard

Hello

I am a beginner when it comes to SQL so maybe I have a trivial question :)

I need to make a SQL SELECT query.

I have Lending table with columns: Lending_ID (integer) Primary Key, Person_ID(integer) Foreign Key, Lending_status_ID(integer).

A person can have many lendings. That means one Person_ID has zero or more lending_ID's and every Lending has Lending_status_ID from 1 to 4.

Person_ID comes from Persons table, but not every Person_ID in Persons table is in Lending table. Every person has a Person_status which is in Persons table.

My SELECT query should output Person_ID's from Persons table which have Person_status set to 'Active' or 'Nonactive' and then show how many lendings each person have with status '2' or '4'.

----------

SELECT Persons.Person_ID, Count(Lending.Person_ID) As LendingNumber
From Persons INNER JOIN Lending ON Persons.Person_ID=Lending.Person_ID
WHERE ((Persons.Person_status="active") OR (Persons.Person_status="nonactive"))
and Lending.Lending_status_ID in(2,4)
GROUP BY Persons.Person_ID
-------

Currently this counts every lending, but it needs to shows nonactive or active persons and how many lendings they have with status 2 or 4.

Thank you

---

SELECT p.Person_ID, Count(l.Lending_ID) As Number
From Persons p
LEFT JOIN Lending l
ON p.Person_ID=l.Person_ID
AND (p.Person_status='active' 
OR p.Person_status='nonactive')
AND (l.Lending_status=2 OR l.Lending_status=4)
GROUP BY p.Person_ID