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);
2
Answers
You could try this, left outer join will do
You can pass the corp_id in where condition if its needed
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.
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.