subject:"Trouble with LEFT JOIN"

Re: Trouble with LEFT JOIN

2015-09-25 Thread Richard Reina

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 :

> On 2015-09-04 11:39 AM, Richard Reina wrote:
>
>
> 2015-09-04 11:18 GMT-05:00 Peter Brawley :
>
>> 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
>
>
>
>
>
>

RE: Trouble with LEFT JOIN

2015-09-25 Thread Adrian Beech

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:gatorre...@gmail.com] 
Sent: Saturday, 26 September 2015 1:09 AM
To: peter.braw...@earthlink.net
Cc: mysql@lists.mysql.com
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 <peter.braw...@earthlink.net>:

> On 2015-09-04 11:39 AM, Richard Reina wrote:
>
>
> 2015-09-04 11:18 GMT-05:00 Peter Brawley <peter.braw...@earthlink.net>:
>
>> 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

Trouble with LEFT JOIN

2015-09-04 Thread Richard Reina

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;
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

Re: Trouble with LEFT JOIN

2015-09-04 Thread Peter Brawley


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

Re: Trouble with LEFT JOIN

2015-09-04 Thread Richard Reina

2015-09-04 11:18 GMT-05:00 Peter Brawley :

> 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
>>
>>
> Got it to work this way:

SELECT c.ID AS ID
FROM challenge c
WHERE c.ID NOT IN ( SELECT ID from patrocinio p WHERE p.PTRN_ID=1 );

Re: Trouble with LEFT JOIN

2015-09-04 Thread Peter Brawley


On 2015-09-04 11:39 AM, Richard Reina wrote:


2015-09-04 11:18 GMT-05:00 Peter Brawley >:


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

Re: Trouble with LEFT JOIN

RE: Trouble with LEFT JOIN

Trouble with LEFT JOIN

Re: Trouble with LEFT JOIN

Re: Trouble with LEFT JOIN

Re: Trouble with LEFT JOIN

6 matches

Site Navigation

Mail list logo

Footer information