CODEWITHSUNDEEP
sql
David542
David542

Reputation: 110173

Compound SQL SELECT

I have the following two tables:

auth_user

useprofile_userprofile

How would I find all auth_user objects where is_active=0 and there is no userprofile_userprofile object with that user_id? For example, an entry like this --

auth_user

and userprofile_userprofile has no object where user_id = 1

Upvotes: 1

Views: 1313

Answers (6)

cctan
cctan

Reputation: 2023

SELECT *
FROM auth_user A
LEFT JOIN userprofile_userprofile B ON A.id=B.user_id
WHERE A.is_active = false and B.user_id IS NULL

when B.user_id is NULL that means it cannot find a row where user_id=1.
This assumes that the id in table userprofile_userprofile are all not NULL.

Upvotes: 3

Amritpal Singh
Amritpal Singh

Reputation: 1785

select au.id,au.username,count(up.id) from auth_user au
left outer join useprofile_userprofile up
on au.id=up.user_id
where is_active = 1 
group by au.id,au.username
having count(up.id)=0

Upvotes: 0

Rob
Rob

Reputation: 49

You are looking for Table Joins. Reference this tutorial:

http://www.tizag.com/mysqlTutorial/mysqljoins.php

To answer your question, you are looking for something along the lines of:

"SELECT auth_user.username,auth_user.is_active,useprofile_userprofile.user_id WHERE is_active = 0 AND user_id != 1"

Upvotes: 0

fancyPants
fancyPants

Reputation: 51888

Apart from the other solutions you can also do it via LEFT JOIN

SELECT
*
FROM
auth_user au
LEFT JOIN useprofile_userprofile uu ON au.id = uu.user_id
WHERE uu.id IS NULL
AND au.is_active = 0

Upvotes: 1

Arion
Arion

Reputation: 31239

SELECT
    *
FROM
    auth_user
WHERE
    auth_user.is_active=0
    AND NOT EXISTS
        (
            SELECT
                NULL
            FROM
                userprofile_userprofile 
            WHERE
                userprofile_userprofile.user_id=auth_user.id
        )

Upvotes: 2

Phil
Phil

Reputation: 42991

select * from auth_user au
where au.is_active = 0 and 
    not exists(select * from userprofile_userprofile uu where uu.user_id = au.user_id)

Upvotes: 2

Related Questions