CODEWITHSUNDEEP
mysqlinner-joinunion
gerl
gerl

Reputation: 1181

join multiple data sets without union

I have 2 tables: book_users and assign_book_users. they look like the image below respectively.

id  book_id  user_id  status
1   11       33       open
2   44       54       closed
3   11       98       pending
4   12       33       open
5   23       99       open
6   24       33       closed
7   25       98       pending
8   26       33       open

id  book_id  user_id  assigner_id  corp_id
1   11       33       55           2345
2   11       33       232          345
3   11       98       55           2345
4   12       33       235          667
5   12       33       77           876
6   12       45       89           2345

I want to get the result below but i can only achieve them using UNIONs. Is there a better way to get the same result without using UNION?

book_id  user_id  assigner_id  status
11       33       55           open
11       98       55           pending
12       45       89           NULL 
44       54       NULL         closed
23       99       NULL         open

This is my query:

SELECT 
    book_users.book_id,
    book_users.user_id,
    assign_book_users.assigner_id,
    book_users.status
    FROM book_users 
inner join assign_book_users on assign_book_users.user_id = book_users.user_id 
    
and assign_book_users.book_id = book_users.book_id and
    assign_book_users.corp_id = 2345 
    union

SELECT 
    assign_book_users.book_id,
    assign_book_users.user_id,
    assign_book_users.assigner_id,
    'NULL as status'
  
    FROM assign_book_users
    where assign_book_users.corp_id = 2345
and assign_book_users.user_id not in (select book_users.user_id from book_users)

union
SELECT 
    book_users.book_id,
    book_users.user_id,
    'NULL as assigner_id',
    book_users.status
  
    FROM book_users
    where book_users.user_id  not in (select assign_book_users.user_id from assign_book_users where
    assign_book_users.corp_id = 2345);

Upvotes: 1

Views: 56

Answers (2)

DRapp
DRapp

Reputation: 48169

A cleaner query to offer, getting similar results based on interpretation of your sample data and query...

It appears you only care about the one Corp_ID = 2345 (for the most part). THEN, getting all books with a status not assigned to anyone from same Corp_id. You would still need a UNION. To get MOST of that, you can do with a left-join. I would start with the assign_book_user table and SEE IF there is something in the other.

select
        abu.book_id,
        abu.user_id,
        abu.assign_id,
        coalesce( bu.status, 'NULL as status' ) status
    from
        assign_book_user abu
            LEFT JOIN book_users bu
                on abu.book_id = bu.book_id
                AND abu.user_id = bu.user_id
    where
        abu.corp_id = 2345
UNION
-- now reverse looking for all books with status and no assigned by
select
        abu.book_id,
        abu.user_id,
        'NULL as assign_id' assign_id,
        bu.status
    from
        book_users bu
        left join assign_book_user abu
                on bu.book_id = abu.book_id
                AND bu.user_id = abu.user_id
                AND abu.corp_id = 2345
    where
        abu.id is null

Now, your query explicitly was looking for Corp_id = 2345. But what if your entry ex: book_id = 44, user_id = 54 did have an assignment, but the assignment was to corp_id = 37. It would be a valid book being assigned, just not to the corporation you intended. Would you still want that? Should it be excluded? Should it be labeled something like

Assigned, but not by corp_id 2345?

Overall, your should EDIT YOUR QUESTIONG. Put in simple English, what you are TRYING to get, and why the strange condition of unassigned. Are they really serving two different purposes? Writing a query is one thing. Having it match what your intention needed is another.

Upvotes: 1

Curious_learner
Curious_learner

Reputation: 53

You could try this, left outer join will do

SELECT distinct a.book_id,a.user_id,b.assigner_id,a.status
FROM book_users a
left outer join assign_book_users b on b.id = a.id and b.user_id = a.user_id and b.book_id = a.book_id

You can pass the corp_id in where condition if its needed

Upvotes: 2

Related Questions