CODEWITHSUNDEEP
sqlcount
Michelle
Michelle

Reputation: 365

Limit Query Result Using Count

I need to limit the results of my query so that it only pulls results where the total number of lines on the ID is less than 4, and am unsure how to do this without losing the select statement columns.

select fje.journalID, fjei.ItemID, fjei.acccount, fjei.debit, fjei.credit
from JournalEntry fje
inner join JournalEntryItem fjei on fjei.journalID = fje.journalID
inner join JournalEntryItem fjei2 on fjei.journalID = fjei2.journalID and   
       fjei.ItemID != fjei2.ItemID
order by fje.journalID

So if journalID 1 has 5 lines, it should be excluded, but if it has 4 lines, I should see it in my query. Just need a push in the right direction. Thanks!

Upvotes: 1

Views: 36

Answers (1)

Dan Bracuk
Dan Bracuk

Reputation: 20794

A subquery with an alias has many names, but it's effectively a table. In your case, you would do something like this.

 select your fields
 from your tables
 join (
 select id, count(*) records
 from wherever
 group by id ) derivedTable on someTable.id = derivedTable.id
     and records < 4

Upvotes: 1

Related Questions