Hi There,
If I interpreted what you are trying to do is to return all the rows from
Challenge and reference Patrocinio excluding those rows in Patrocinio where
PTRN_ID is 1?
Not sure if the below is possible in MySQL but I've used this in other places
when doing a left join and needing to exclude specific rows when satisfying the
join condition.
select ...
from table_1 t1
left join table_2 t2 on t1.id = t2.id and not t2.fld = 1
where ...
Apologies I don't have a MySQL instance to test the above and it's been quite a
while since I've dabbled seriously with this database.
HTH
Cheers.
AB
-----Original Message-----
From: Richard Reina [mailto:[email protected]]
Sent: Saturday, 26 September 2015 1:09 AM
To: [email protected]
Cc: [email protected]
Subject: Re: Trouble with LEFT JOIN
Peter,
Thank you very much for your reply. Three weeks later I am realizing that
the 'NOT IN' solution I had above does not work after all. Accordingly, I
went back and looked at your solution and it is close but it only gives
NULL results I am looking for something that excludes challenges that have
are linked to p.PTRN_ID=1. So p.PTRN_ID!=1. Thanks nonetheless for trying.
This may be unsolvable.
2015-09-04 12:30 GMT-05:00 Peter Brawley <[email protected]>:
> On 2015-09-04 11:39 AM, Richard Reina wrote:
>
>
> 2015-09-04 11:18 GMT-05:00 Peter Brawley <[email protected]>:
>
>> 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
>>
>> -----
>>
>>
>
> Hi Peter,
>
> Thanks for the reply. Along those lines I have also tried:
>
> select c.ID FROM challenge c LEFT JOIN ( SELECT p.ID FROM patrocinio WHERE
> p.PTRN_ID=1 ) p ON p.CHLG_ID=c.ID WHERE p.PTRN_ID IS NULL;
>
> But that's not working either.
>
>
> drop table if exists patrocinio, challenge;
> create table challenge(
> id smallint,plr_id smallint,acc_type_id smallint,
> season char(4), year year, char_id smallint );
> insert into challenge values
> ( 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);
> create table patrocinio(
> id smallint, ptrn_id smallint, chlng_id smallint, amount decimal(6,2) );
> insert into patrocinio values
> ( 1 , 1 , 1 , 1.00 ),
> ( 2 , 4 , 3 , 2.00 ),
> ( 3 , 3 , 6 , 1.00 );
>
> select c.id , p.chlng_id
> from challenge c
> left join patrocinio p on c.id=p.chlng_id ;
> +------+----------+
> | id | chlng_id |
> +------+----------+
> | 1 | 1 |
> | 3 | 3 |
> | 6 | 6 |
> | 2 | NULL |
> | 4 | NULL |
> | 5 | NULL |
> +------+----------+
>
> select c.id , p.chlng_id
> from challenge c
> left join patrocinio p on c.id=p.chlng_id
> where p.chlng_id is null;
> +------+----------+
> | id | chlng_id |
> +------+----------+
> | 2 | NULL |
> | 4 | NULL |
> | 5 | NULL |
> +------+----------+
>
> PB
>
>
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
