How to find exact match item with AND condition from left outer join

Question

I have a MySQL table
Booktable

+--------+-------------+-----+  
| bookno | bookname    | ... |  
+--------+-------------+-----+  
| 1      | FINALFANTASY| ... |  
+--------+-------------+-----+

Authortable

+--------+-------------+-----+  
| bookno | Authorname  | ... |  
+--------+-------------+-----+  
| 1      | SQUARE      | ... |  
+--------+-------------+-----+  
| 1      | ENIX        | ... |  
+--------+-------------+-----+

so I would like to make a search condition to get the book that match with the result.

I try with

select b.bookname,a.authorname from booktable as b 
left outer join authortable a on b.bookno = a.bookno
where a.authorname = "square" and a.authorname = "enix"

It only work with only one where condition.but when I try with two authorname there is no result found. what should I do ?

(this query it working with "OR" but not "AND" but I really want the value that match the search condition or if there are some search condition that not match but not blank it should not be showing(so or it not working in this case)

Are you sure you want to use `LEFT OUTER JOIN`, not `INNER JOIN`? This will return all books, with `NULL` in the `Authorname` column when it's not one of the authors you're searching for. — Barmar, Jan 19 '17 at 02:10

Tim Biegeleisen · Answer 1 · 2017-01-19T02:48:14.087

4

Use aggregation to identify which books have both the authors you want:

SELECT t1.bookname,
       t2.authorname
FROM booktable t1
INNER JOIN authortable t2
    ON t1.bookno = t2.bookno
INNER JOIN
(
    SELECT bookno
    FROM authortable
    WHERE authorname IN ('square', 'enix')
    GROUP BY bookno
    HAVING COUNT(DISTINCT authorname) = 2
) t3
    ON t1.bookno = t3.bookno

Demo here:

SQLFiddle

edited Jan 19 '17 at 02:48

answered Jan 19 '17 at 02:15

Tim Biegeleisen

502,043
27
286
360

This SQL will also return trun when book's author in ('squre', 'enix', 'othername') and I assume and questioner is OK with this situation. – SIDU Jan 19 '17 at 03:32
@SIDU No it won't, because in this case the distinct count would be 3, not 2, and such a book would not be added. But we can easily modify the query to handle the case you describe as well. – Tim Biegeleisen Jan 19 '17 at 03:33
I m afraid not :D – SIDU Jan 19 '17 at 03:39

SIDU · Accepted Answer · 2017-01-19T05:58:35.477

Tim Biegeleisen's answer is great, but in case you need exactly match, the the last SQL in the following is correct:

SELECT * FROM book;
SELECT * FROM author;

/* this SQL will return book's author name more than 2 also true */
SELECT b.bookname, a.authorname
FROM book AS b
JOIN author AS a ON b.bookno = a.bookno
JOIN (
  SELECT bookno FROM author
  WHERE authorname in ('SQUARE', 'ENIX')
  GROUP BY 1
  HAVING count(*) = 2
) AS a2 ON b.bookno = a2.bookno;

/* this sQL will return only 2 and all matched authors: */
SELECT b.bookname, a.authorname
FROM book AS b
JOIN author AS a ON b.bookno = a.bookno
JOIN (
  SELECT bookno FROM author
  WHERE authorname in ('SQUARE', 'ENIX')
  GROUP BY 1
  HAVING count(*) = 2
) AS a2 ON b.bookno = a2.bookno
JOIN (
  SELECT bookno FROM author 
  GROUP BY 1 
  HAVING count(distinct authorname) = 2
) AS a3 ON b.bookno = a3.bookno

PS1 - no need left join

PS2 - no need count distinct - unless your author table not design properly

If title is FANTASY genre is Adventure,fantasy, and search condition is

[ADVENTURE] = found

[FANTASY] = found

[ADVENTURE,FANTASY] = found

[ADVENTURE,FANTASY,ACTION] = not found

Then the SQL will be:

SELECT b.bookname, a.authorname
FROM book AS b
JOIN author AS a ON b.bookno = a.bookno
JOIN author AS a1 ON b.bookno = a1.bookno AND a1.authorname = 'SQUARE'
JOIN author AS a2 ON b.bookno = a2.bookno AND a2.authorname = 'ENIX'

Above is working, and I m wondering if there is a performance improvement

Thank you very much but I want to know more about I want a table that displays book information that perfectly matches the search condition. my Perfect match means that the search condition is perfectly matched for each item. Also, since the item is an AND search excluding the search condition that is blanked, if there is an item that does not exactly match the search condition in items other than blanks, the book information is excluded. — Shirokuma1205, Jan 19 '17 at 05:16
`If title is FANTASY genre is Adventure,fantasy if search condition is [ADVENTURE] = found [FANTASY] = found [ADVENTURE,FANTASY] = found [ADVENTURE,FANTASY,ACTION] = not found` — Shirokuma1205, Jan 19 '17 at 05:40

How to find exact match item with AND condition from left outer join

2 Answers2

SQLFiddle