SQL - query tables related in two different ways
SQL - query tables related in two different ways
Imagine I have a MySQL database for managing a library, and I want to know about all members that have either borrowed or reserved it. I am looking for a single query, sorted by book ID.
The tables are book, borrow, reserve and member. Both the borrow and reserve tables contain a book ID and member ID.
I only want one unique pair of book ID and member ID in the result. This means these cases need to be deduplicated
a member reserved a book and borrowed it also
a member borrowed or reserved the book multiple times
Can anyone help with this?
Will make an SqlFiddle
– Ben E.
Jul 1 at 4:20
1 Answer
1
You can use UNION, as it eliminates repeated results.
UNION
SELECT user_id, book_id FROM borrow
UNION
SELECT user_id, book_id FROM reserve;
You can refer SQLFiddle example here: http://sqlfiddle.com/#!17/b8b44/7
SQLFiddle
Wow too fast nice work! Join was the wrong approach.
– Ben E.
Jul 1 at 4:44
The
DISTINCT in each select is useless as UNION already implies a DISTINCT– a_horse_with_no_name
Jul 1 at 5:01
DISTINCT
UNION
DISTINCT
a)
DISTINCT is unnecessary, b) it is not specific for PostgreSQL– Ingaz
Jul 1 at 6:16
DISTINCT
PostgreSQL
Indeed
DISTINCT is not needed here, my bad. Updated my answer, thanks!– Shuwn Yuan Tee
Jul 1 at 6:34
DISTINCT
@a_horse_with_no_name it becomes useful as soon as you select another column, if that column is not part of a condition in the WHERE clause, the DISTINCT becomes required
– Ben E.
Jul 1 at 6:39
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
please show us sample table structure with data in it.
– Mohit Kumar
Jul 1 at 4:03