OUTER JOIN Trouble

  • Thread starter Thread starter Andreas
  • Start date Start date
A

Andreas

Hello

I have a question about outer join.

For example, we have Persons table:

Person_id | Status |
1 | active
2 | with obligations
3 | nonactive
4 | active

Then we have Lending table
Lending_id | Person_id | Lending_status_ID |
1 | 1 | 1
2 | 1 | 1
3 | 2 | 1
4 | 2 | 2
5 | 3 | 3
6 | 4 | 3
7 | 4 | 1
8 | 1 | 3

This is the result what I aim:
Person_ID | How_many_lendings_with_status_2_or_1 |
3 | 0
4 | 1
1 | 2

In other words, I want to 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 '1' or '2'.

This is what I have:

SELECT Persons.Person_ID, Count (Lending.Person_ID) AS LendingNumber
FROM Persons
LEFT OUTER JOIN Lending ON Persons.Person_ID = Lending.Person_ID
AND Lending.Lending_status_ID in (2,4)
WHERE Persons.Person_status IN ('active', 'nonactive')
GROUP BY Persons.Person_ID

However, Access has trouble with "Lending.Lending_status_ID in (2,4)" part.

Are there other ways to specify that to count only lendings with status 2 or
4?

Best Regards
 
Andreas said:
I have a question about outer join.

For example, we have Persons table:

Person_id | Status |
1 | active
2 | with obligations
3 | nonactive
4 | active

Then we have Lending table
Lending_id | Person_id | Lending_status_ID |
1 | 1 | 1
2 | 1 | 1
3 | 2 | 1
4 | 2 | 2
5 | 3 | 3
6 | 4 | 3
7 | 4 | 1
8 | 1 | 3

This is the result what I aim:
Person_ID | How_many_lendings_with_status_2_or_1 |
3 | 0
4 | 1
1 | 2

In other words, I want to 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 '1' or '2'.

This is what I have:

SELECT Persons.Person_ID, Count (Lending.Person_ID) AS LendingNumber
FROM Persons
LEFT OUTER JOIN Lending ON Persons.Person_ID = Lending.Person_ID
AND Lending.Lending_status_ID in (2,4)
WHERE Persons.Person_status IN ('active', 'nonactive')
GROUP BY Persons.Person_ID

However, Access has trouble with "Lending.Lending_status_ID in (2,4)" part.

Are there other ways to specify that to count only lendings with status 2 or
4?
Click to expand...


You can put non varying conditions in the Where clause.

Since the statusID and status are equivalent, there really
is no need to join to the persons table. Shortening a few
things, this should do what you asked:

SELECT Person_ID,
Count (*) AS LendingNumber
FROM Lending
WHERE Lending_status_ID In (2,4)
GROUP BY Person_ID
 
You can put non varying conditions in the Where clause.

Since the statusID and status are equivalent, there really
is no need to join to the persons table. Shortening a few
things, this should do what you asked:

SELECT Person_ID,
Count (*) AS LendingNumber
FROM Lending
WHERE Lending_status_ID In (2,4)
GROUP BY Person_ID
Click to expand...

Marsh:

I don't think so. Status appears to be an attribute of Persons,
Lending_status_ID of Lending. Person 4 has a status of 'active' but
lendings with Lending_status_ID values of 3 and 1 for instance. So
both tables are required:

SELECT Persons.Person_ID, COUNT (*) AS LendingNumber
FROM Persons INNER JOIN Lending
ON Persons.Person_ID = Lending.Person_ID
WHERE Persons.Person_status IN ('active', 'nonactive')
AND Lending.Lending_status_ID in (2,4)
GROUP BY Persons.Person_ID;

The join is an inner one of course, not an outer one.

Ken Sheridan
Stafford, England
 
Ken said:
[]
Marsh:

I don't think so. Status appears to be an attribute of Persons,
Lending_status_ID of Lending. Person 4 has a status of 'active' but
lendings with Lending_status_ID values of 3 and 1 for instance. So
both tables are required:

SELECT Persons.Person_ID, COUNT (*) AS LendingNumber
FROM Persons INNER JOIN Lending
ON Persons.Person_ID = Lending.Person_ID
WHERE Persons.Person_status IN ('active', 'nonactive')
AND Lending.Lending_status_ID in (2,4)
GROUP BY Persons.Person_ID;

The join is an inner one of course, not an outer one.
Click to expand...


You're probably right about which status is an attribute of
which entity.

Either way the trick is to use the WHERE clause instead of
the ON phrase.

In this case and performance aside, whether its an inner or
outer join doesn't matter because that where clause will
cause an outer join to produce the same result set as an
inner join.
 
Back
Top