CODEWITHSUNDEEP
phpmysqljoin
Al-Alamin
Al-Alamin

Reputation: 1478

Should i use Join or use seperate queries

Table 1: book(bookid(pk),bookname,edition)
Table 2: bookinfoauthors(bookid(fk), authorid(fk))
Table 3: author(authorid(pk), authorname)

I have a array of AuthorNames I want to get the corresponding bookName written by those authors AuthorNames might contain 1 or more names

I am using php and mysql Database

Style 1:

select book.bookName
from book, bookauthors, author
where  (book.bookid = bookoauthors.bookid)
and (author.authorid = bookauthor.authorid)
and (author.authorName = Authornames[0])
or (author.authorName = Authornames[1])
or (author.authorName = Authornames[2])
or (author.authorName = Authornames[3])

As i am using php my mysql; Style 2: // suppose $authorId, $bookId, $bookName contains array of int or string not objects

$authorId  = 
    select authorId
    from authors
    where authorName in ($authorNames);

$bookId = select bookid from bookAuthors where bookName in($authorId);
$bookName = select bookName from book where bookid in (bookId);

In second style I am not using joins which one will be efficient and what should I follow

Upvotes: 1

Views: 69

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

First I would say that you almost certainly would rather do a single JOIN query to get a single result set than making many different calls to your MySQL database. MySQL was designed for heavy lifting of data; PHP much less so. The time spent in network traffic could be substantial in the latter case, impacting the performance of your site.

Second you should try to use ANSI-92 compliant SQL queries. So I would rewrite your JOIN query as this:

SELECT b.bookName
FROM book b INNER JOIN bookauthors ba ON b.bookid = ba.bookid
            INNER JOIN author a ON a.authorid = ba.authorid
WHERE a.authorName = Authornames[0] OR
      a.authorName = Authornames[1] OR
      a.authorName = Authornames[2] OR
      a.authorName = Authornames[3]

The reason this style of query is preferred is that it separates the joining of tables from the other restrictions in the WHERE clause. In your original query the join criteria and restrictions both appear together in the WHERE clause making it harder to read.

Upvotes: 2

Related Questions