CODEWITHSUNDEEP
mysqlsql
Fappie.
Fappie.

Reputation: 490

SQL result addition

I have database with table :

Document is having loanId and documentTeplateId as foreign key.

Every loan has multiple documents.

I want to select every loan (or loanId), that don't have document with teplateId 100.

Currently I'm stuck with this following sql:

SELECT l.id as loanId, d.id as documentId, d.document_templateid as documentTeplateId 
FROM loan as l
LEFT JOIN document as d ON (d.loanid = l.id)
WHERE d.document_templateid != 100
ORDER BY loanId DESC

It returns me something like this, obviously..

enter image description here

But that's not what I want.

Any suggestions please?

Upvotes: 1

Views: 57

Answers (3)

Andrew Castellano
Andrew Castellano

Reputation: 383

SELECT l.id as loanId
FROM loan as l
LEFT JOIN document as d
ON (d.loanid = l.id)
WHERE d.document_templateid != 100
ORDER BY loanId  DESC
GROUP BY loanId

"GROUP BY loanId" will group rows that have the same loanId into one row, removing the duplicates. You can only select loan.id for this to work, which fits well for your scenario since you seem to indicate this is the only value you need.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I think you want aggregation and a having` clause:

SELECT l.id as loanid
FROM loan l LEFT JOIN
     document d
     ON d.loanid = l.id
GROPU BY l.id
HAVING SUM( d.document_templateid = 100 ) = 0;

If you only want to consider loans that have documents, then the JOIN is not needed:

SELECT d.loanid
FROM document d
GROPU BY d.loanid
HAVING SUM( d.document_templateid = 100 ) = 0;

Upvotes: 0

forpas
forpas

Reputation: 164099

You need only the distinct loan ids:

SELECT distinct l.id as loanid
FROM loan as l
LEFT JOIN document as d
ON (d.loanid = l.id)
WHERE d.document_templateid != 100
ORDER BY loanId desc

Upvotes: 1

Related Questions