SQL - How do I select rows from one table depending on data from two other tables?

Question

I have an SQL question. It's a simple problem, but I'm not an SQL guy at all.

Here is the situation, I have three tables:

CUSTOMER(
    PK(customer_id)
)

LOAN(
    PK(loan_id),
    customer_it,
    behavior_id
)

BEHAVIOR(
    PK(behavior_id),
    unpaid_number
)

// PK(x): x is a primary key.

I would like to select all of the CUSTOMERs who have an unpaid_number >= 1.

Can anybody show me a way to work this around?

Thanks

This looks very much like homework. I agree with apomene's answer, but you could have shown what you have tried already. This is a very basic task, that is easy to figure out using a beginners tutorial. — 9DA, May 03 '18 at 14:37

apomene · Answer 1 · 2018-05-03T14:33:49.493

7

You are looking for INNER JOIN. Use like:

SELECT * FROM CUSTOMER c
INNER JOIN LOAN l ON c.customer_id = l.customer_it
INNER JOIN BEHAVIOR b ON b.behavior_id = l.behavior_id
WHERE b.unpaid_number>=1

edited May 03 '18 at 14:33

answered May 03 '18 at 14:32

apomene

14,282
9
46
72

score 2 · Answer 2 · answered May 03 '18 at 14:36

Actually, if you want all customers, you presumably want one row per customer, regardless of the number of matching rows in behavior.

That would suggest using exists or in:

select c.*
from customer c
where exists (select 1
              from loan l join
                   behavior b
                   on b.behavior_id = l.behavior_id
              where b.unpaid_number >= 1 and
                    l.customer_id = c.customer_id
             );

This is particularly important if you are considering using select distinct.

score 2 · Accepted Answer · answered May 03 '18 at 14:58

2

Use inner join

SELECT c.* FROM CUSTOMER c INNER JOIN LOAN l ON l.customer_id = c.Customer_id INNER JOIN BEHAVIOR b ON b.behavior_id = l.behavior_id WHERE unpaid_number >=1

answered May 03 '18 at 14:58

Sandeep Kumar

46
2

score 1 · Answer 4 · edited May 03 '18 at 14:55

1

Please, try below code

SELECT c.*
FROM CUSTOMER c
INNER JOIN LOAN l
    ON l.customer_id = c.Customer_id
INNER JOIN BEHAVIOR b
    ON b.behavior_id = l.behavior_id
WHERE  unpaid_number >=1

edited May 03 '18 at 14:55

Joe Taras

15,166
7
42
55

answered May 03 '18 at 14:35

Rajat Jaiswal

645
4
15

score 1 · Answer 5 · edited May 03 '18 at 14:55

1

try this?

SELECT LOAN.customer_it FROM LOAN
WHERE LOAN.behavior_id IN
    (SELECT BEHAVIOR.behavior_id
    from BEHAVIOR where BEHAVIOR.unpaid_number>=1)

edited May 03 '18 at 14:55

Joe Taras

15,166
7
42
55

answered May 03 '18 at 14:43

Mustahsan

3,852
1
18
34

SQL - How do I select rows from one table depending on data from two other tables?

5 Answers5