Web lists-archives.com

Re: Trouble with LEFT JOIN




On 2015-09-04 9:40 AM, Richard Reina wrote:
I have the following two tables;

mysql> select * from challenge;
+----+--------+-------------+--------+------+---------+--------------+
| ID | PLR_ID | ACC_TYPE_ID | season | year | CHAR_ID |
+----+--------+-------------+--------+------+---------+--------------+
|  1 |           1 |                      1 | Fall      | 2015 |       1175
|
|  2 |           1 |                      4 | Fall      | 2015 |       1175
|
|  3 |           1 |                      3 | Fall      | 2015 |       1175
|
|  4 |           1 |                    10 | Fall      | 2015 |       1175 |
|  5 |           1 |                    13 | Fall      | 2015 |       1175 |
|  6 |           1 |                      2 | Fall      | 2015 |       1175
|
+----+----------+----------------------+--------+-------+-------------+
6 rows in set (0.00 sec)

mysql> select * from patrocinio;
+----+------------+---------------+-------------+
| ID | PTRN_ID | CHLNG_ID | AMOUNT |
+----+------------+---------------+-------------+
|  1 |             1 |                 1 |   1.00      |
|  2 |             4 |                 3 |   2.00      |
|  3 |             3 |                 6 |   1.00      |
+----+-----------+-----------------+------------+
I would like to select all rows from challenges which are NOT linked to a
patrocinio with the PTRN_ID -- which would be rows 2 through 6 of
challenges.

I am trying to go about this with a LEFT JOIN query but it does not seem to
be working for me.

mysql> select c.ID FROM challenge c LEFT JOIN patrocinio p ON
c.ID=p.CHLNG_ID WHERE p.PTRN_ID!=1;

... where p.chlng_id IS NULL;

(Look up exclusion joins)

PB

-----

Empty set (0.00 sec)

Instead of getting rows 2 through 6 of challenges I get no rows.

Any help on how to correctly do this query would be greatly appreciated.

Thanks



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql