Is There Any Rule of Thumb to Construct SQL Query from a Human-Readable Description

SQL relationships, multiple methods, same result? Select, From, Where OR Select, From, Join, Where

If you only want to get the answers, don't involve the questions table.
Just select from the answers.

Adding unused tables into your query makes no sense at all -
It makes the query harder to read, thus harder to maintain,
and It makes the database work harder (though modern databases might just optimize the unused parts of the query away) to get the same results.

What's the purpose of a JOIN where no column from 2nd table is being used?

1. Join (any inner, left or right) can duplicate rows if join key in joined dataset is not unique. For example if a contains single row with c_id=1 and b contains two rows with c_id=1, the result will be two rows with a.c_id=1.
2. Join (inner) can filter rows if join key is absent in joined dataset. I believe this is what it meant to do.

If the goal is to get only rows with keys present in both datasets(filter) and you do not want duplication, and you do not use columns from joined dataset, then better use LEFT SEMI JOIN instead of JOIN, it will work as filter only even if there are duplicated keys in joined dataset:

left semi join (
                select c_id from second
               ) b on a.c_id = b.c_id 

This is much safer way to filter rows only which exist in both a and b and avoid unintended duplication.

You can replace join with WHERE IN/EXISTS, but it makes no difference, it is implemented as the same JOIN, check the EXPLAIN output and you will see the same query plan. Better use LEFT SEMI JOIN, it implements uncorrelated IN/EXISTS in efficient way.

If you prefer to move it to the WHERE:

WHERE a.c_id IN (select c_id from second)

or correlated EXISTS:

WHERE EXISTS (select 1 from second b where a.c_id=b.c_id)

But as I said, all of them are implemented internally using JOIN operator.

Mysql Left Join Case When

You can't do a case/when join as you have here. Since you have a split way of doing the HTML link references, and each has its own context of a biblio record, you might be best to get the pieces from two distinct queries of biblio vs non-biblio and UNION them. Take that result and apply to your concat process. Something like

SELECT 
        PQ.action_id, 
        PQ.barcode, 
        CONCAT( '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', 
                PQ.biblionumber, '\">', b.title, '</a>') Title, 
        PQ.author, 
        PQ.timestamp, 
        CONCAT( '<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', 
                b.borrowernumber, '\">', b.surname, ', ', 
                b.firstname, '</a>') Librarian, 
        PQ.permanent_location, 
        PQ.typeofentry, 
        PQ.action as type_of_action, 
        PQ.info 
    from
        (
        SELECT 
                action_id, 
                i.barcode, 
                biblio.biblionumber,
                biblio.author, 
                al.timestamp, 
                i.permanent_location, 
                'biblio' typeofentry, 
                al.action, 
                al.info,
                al.user
            from 
                action_logs al 
                    LEFT JOIN items i 
                        on al.object = i.itemnumber 
                    LEFT JOIN biblio 
                        on al.object = biblio.biblionumber
            where
                al.info like 'biblio%'
        UNION ALL
        SELECT 
                action_id, 
                i.barcode, 
                biblio.biblionumber,
                biblio.author, 
                al.timestamp, 
                i.permanent_location, 
                'item' typeofentry, 
                al.action, 
                al.info,
                al.user
            from 
                action_logs al 
                    LEFT JOIN items i 
                        on al.object = i.itemnumber 
                        LEFT JOIN biblio 
                            on i.biblionumber = biblio.biblionumber
            where
                NOT al.info like 'biblio%'
        ) PQ
            LEFT JOIN borrowers b
                ON PQ.user = b.borrowernumber 

Join queries properly - SQL

Select the books and join the co-author count:

select
  b.title,
  case when b.author is null then 0 else 1 end as has_author,
  coalesce(c.cnt, 0) as count_co_author
from books b
left outer join
(
  select book, count(*) as cnt
  from co_author
  group by book
) c on c.book = b.isbn
order by b.title;

Or select from books and get the co-author count in a subquery:

select
  b.title,
  case when b.author is null then 0 else 1 end as has_author,
  (
    select count(*)
    from co_author c
    where c.book = b.isbn
  ) as count_co_author
from books b
order by b.title;

---

Related Topics