MySQL Left Outer Join, Exclude items from the second table owned by the user

I have two tables in my MySQL database, one is a library of all of the books in the database, and the other is containing individual rows corresponding to which books are in a user's library.

For example:

Library Table

 `id`        `title`...
=====        ===========
  1          Moby Dick
  2          Harry Potter

Collection Table

 `id`        `user`      `book`
=====        ======      =======
  1            1            2
  2            2            2
  3            1            1

What I want to do is run a query that will show all the books that are not in a user's collection. I can run this query to show all the books not in any user's collection:

SELECT *
FROM  `library`
LEFT OUTER JOIN  `collection` ON  `library`.`id` =  `collection`.`book`
WHERE  `collection`.`book` IS NULL

This works just fine as far as I can tell. Running this in PHPMyAdmin will result in all of the books that aren't in the collection table.

However, how do I restrict that to a certain user? For example, with the above dummy data, I want book 1 to result if user 2 runs the query, and no books if user 1 runs the query.

Just adding a AND user=[id] doesn't work, and with my extremely limited knowledge of JOIN statements I'm not getting anywhere really.

Also, the ID of the results being returned (of query shown, which doesn't do what I want but does function) is 0-- how do I make sure the ID returned is that of library.id?

---

You'll have to narrow down your LEFT JOIN selection to only the books that a particular user has, then whatever is NULL in the joined table will be rows(books) for which the user does not have in his/her collection:

SELECT
    a.id,
    a.title
FROM
    library a
LEFT JOIN
    (
        SELECT book
        FROM collection
        WHERE user = <userid>
    ) b ON a.id = b.book
WHERE
    b.book IS NULL

An alternative is:

SELECT
    a.id,
    a.title
FROM
    library a
WHERE
    a.id NOT IN
    (
        SELECT book
        FROM collection
        WHERE user = <userid>
    )

However, the first solution is more optimal as MySQL will execute the NOT IN subquery once for each row rather than just once for the whole query. Intuitively, you would expect MySQL to execute the subquery once and use it as a list, but MySQL is not smart enough to distinguish between correlated and non-correlated subqueries.

As stated here:

"The problem is that, for a statement that uses an IN subquery, the optimizer rewrites it as a correlated subquery."

Mysql LEFT OUTER JOIN get NULL on the right table when there are records

Below is my left outer join query :

    SELECT  N.ACCOUNT, N.FLD1, N.FLD2, P.ACCOUNT,P.FLD1, P.FLD2, P.FLD3, P.FLD4,     P.FLD5, P.FLD6 

FROM NEWYORK N LEFT OUTER JOIN PITTSBURG P  ON   N.ACCOUNT = P.ACCOUNT
WHERE N.FLD1 in ('EC','BP','J1','MP','C1','BP','AD','E1' )
AND N.FLD2 = 'CHICAGO'
GROUP BY
  N.ACCOUNT, N.FLD1, N.FLD2,  P.ACCOUNT,P.FLD1, P.FLD2, P.FLD3, P.FLD4, P.FLD5, P.FLD6

(1)MY query is getting me (null) in P.ACCOUNT,P.FLD1, P.FLD2, P.FLD3, P.FLD4, P.FLD5, P.FLD6. (2) when I do [select * from PITTSBURG P - this has all the accounts that match with NEWYORK N account and all of them P.ACCOUNT,P.FLD1, P.FLD2, P.FLD3, P.FLD4, P.FLD5, P.FLD6 have data ).

Wondering my query is returning (null) records when there are actually match records in the right table .

Thanks very much for your help

---

The problem should be in the where clauses, if the records match like you said in the field ACCOUNT, you are getting null for the where clauses, may be when you applied the where clauses, the result rows in the NEWYORK table does't have a corresponding match in the field ACCOUNT with the PITTSBURG table, test to delete one by one the where clauses and see the result.

Mysql: The SQL subquery does not return what I expect

I have this problem to solve: If two students A and B are friends, and A likes B but not vice-versa, remove the Likes entry.

For background, the Friend table has two columns, STU1 and STU2. If they are friends, then there will be an entry showing STU1, STU2 AND STU2, STU1.

In the Likes table, if Student A likes Student B, there will be an entry for STU1, STU2, but if Student B does not like Student A, there will NOT be an entry for STU2, STU1.

So, here is what I have tried. The problem is that it still leaves two rows in the Likes table that should be out of there. Any ideas on how to solve this on?

delete from Likes
where exists
    (select F.STU1, F.STU2 from Friend F
        where exists
        (select L.STU1, L.STU2 from Likes L, Friend F where
            F.STU1 = L.STU1 and F.STU2 = L.STU2)
        )
    and not exists
        (select L.STU1, L.STU2 from Likes L, Friend F where
            F.STU1 = L.STU2 and F.STU2 = L.STU1)

---

edit:

with onewayfriends as (
select f.* from friend f
left outer join likes l1 on l1.stu1=f.stu1 and l1.stu2=f.stu2
left outer join likes l2 on l2.stu1=f.stu2 and l2.stu2=f.stu1
where l1.stu1 is null or l2.stu1 is null)

delete l from likes l, onewayfriends f
where l.stu1 in (f.stu1, f.stu2) and l.stu2 in (f.stu1, f.stu2)

edit 2, since it's sqllite rewrite the cte as a nested query and the delete with join as a 'where row id in..'

delete from likes where rowid in (select l.rowid from
(
  select f.* from friend f
  left outer join likes l1 on l1.stu1=f.stu1 and l1.stu2=f.stu2
  left outer join likes l2 on l2.stu1=f.stu2 and l2.stu2=f.stu1
  where l1.stu1 is null or l2.stu1 is null
) f, likes l
where l.stu1 in (f.stu1, f.stu2) and l.stu2 in (f.stu1, f.stu2))

LEFT OUTER JOIN does not return null values

I have a pretty simple sample query that I am trying to complete.

SQL Fiddle

SELECT
    month(s.report_date),
    COALESCE(COUNT(*),0)
FROM
    stat_summary s LEFT OUTER JOIN ref_months m on MONTH(s.report_date) = m.month_id
GROUP BY month(s.report_date)

My results look like:

My desired results would be something like:

month  | count
----------------
    1  | 0
    2  | 0
    3  | 0
    4  | 0
    5  | 0
    6  | 0
    7  | 0
    8  | 0
    9  | 4
    10 | 9
    11 | 0
    12 | 0

---

You need to use the months table as the primary one:

SELECT
    m.month_id,
    COALESCE(COUNT(s.report_date),0)
FROM ref_months m
LEFT JOIN stat_summary s
    ON MONTH(s.report_date) = m.month_id
GROUP BY m.month_id;