Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
wippard
Starting Member
1 Post |
Posted - 2008-12-25 : 14:05:27
|
HelloI 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(*) As NumberFrom Persons INNER JOIN Lending ON Persons.Person_ID=Lending.Person_IDWHERE ((Persons.Person_status="active") OR (Persons.Person_status="nonactive"))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 |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-25 : 14:55:43
|
quote: Originally posted by wippard HelloI 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 LendingNumberFrom Persons INNER JOIN Lending ON Persons.Person_ID=Lending.Person_IDWHERE ((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
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-27 : 11:39:20
|
[code]SELECT p.Person_ID, Count(l.Lending_ID) As NumberFrom Persons pLEFT JOIN Lending lON p.Person_ID=l.Person_IDAND (p.Person_status='active' OR p.Person_status='nonactive')AND (l.Lending_status=2 OR l.Lending_status=4)GROUP BY p.Person_ID[/code] |
|
|
|
|
|
|
|