The outer outer join does not work on the column with id repeated

This is table1:

This is table2:

I wanted to see this result:

I wrote this query:

select title, value
from table1
left outer join table2
    on table1.id = table2.id
where category="good"

But it gives me this result:

So, what query should I use to get result with title c coming with ""? (empty string)

---

Move the predicate on table2.category to the ON clause, rather than the WHERE clause.

(In the WHERE clause, that negates the "outerness" of the LEFT JOIN operation, since any rows from table1 with no matching row from table2 would have values of NULL for the table2 columns. Checking for a non-null value excludes all the "unmatched" rows, rendering the LEFT JOIN equivalent to an INNER JOIN.

One way to return the specified resultset:

SELECT t.title
     , s.value
  FROM table1 t
  LEFT
  JOIN table2 s
    ON s.id = t.id
   AND s.category = "good"

The outer join does not indicate the desired lines (null)

I have a table which contains User IDs, Period IDs and Qty:

User Period Qty
---- ------ ---
   1  11201   3
   1  11202   2
   1  11203   5
   2  11202   4
   2  11203   1

Note: User 2 started in Period 11202 so he/she does not have a row for Period 11201

I need to get the Users only from the latest Period...

SELECT User FROM table WHERE Period = 11203

...and then use these User IDs on the same table to get Period 11201 and 11202 Qty for both users.

The problem: I need to see the (null) Qty for User 2 in Period 11201. Like so:

User Period    Qty
---- ------  -----
   1  11201      3
   1  11202      2
   2  11201 (null)
   2  11202      4

I can't figure out the query/joins to achieve it. So far I have this but predictably it gives me only 3 rows (User 1, Periods 11201/11202 and User 2, Period 11202):

SELECT a.User, a.Period, a.Qty
FROM (SELECT User FROM table WHERE Period = 11203) b
LEFT JOIN table a
ON b.User = a.User
WHERE a.Period BETWEEN 11201 AND 11202

---

You need to get all the required periods and make a Cartesian product of these with the Users first, and then outer join to that:

SELECT b.User, p.Period, a.Qty
FROM (SELECT User FROM table WHERE Period = 11203) b
CROSS JOIN (SELECT DISTINCT Period FROM table
            WHERE Period BETWEEN 11201 AND 11202) p
LEFT JOIN table a
ON a.User = b.User
AND a.Period = p.Period

Note also the SELECT needs to get the User and Period from the outer table not the inner one, otherwise everything will be null on the rows where the user is missing.

MariaDB: LEFT OUTER JOIN does not return the line advertisements

I already tried various types of JOINS but I am not able to get this simple query to work. I would like to have the result of table a in any case, even if there is no corresponding entry in table b. I tried:

SELECT a.user_id,
       a.user_name,
       b.first_name
FROM   users a
LEFT OUTER JOIN members b
ON a.member_uid = b.uid
WHERE  (a.user_name = 'TEST'
    AND b.active = 1)

In this case, there is no entry in b that has b.active = 1. But I assumed that all wanted columns from a would be returned and the column from b would be null. But when running this query in the SQL window of the MariaDB, zero rows are returned.

Any help would be highly appreciated!!

---

Left Outer Join will get all the rows/data from table a whether they are matching or not-matching in table b. But you are again filtering out the data by putting conditions in where clause. Since, there is no entry in b that has b.active = 1 so there will be no output. Remove b.active = 1 from the query, like this :

SELECT a.user_id,
   a.user_name,
   b.first_name
FROM   users a
LEFT OUTER JOIN members b
ON a.member_uid = b.uid
WHERE a.user_name = 'TEST';