NYCPHP Meetup

[Carlos A Hoyos]

Nice try, but mysql doesn't support subselects... :(

You can use left joins:

SELECT aaa.a from aaa LEFT JOIN bbb on bbb.b = aaa.a where bbb.b IS NULL ;

this query will return all values in a that are not in b.

enjoy!



                                                                                                                                    
                      "Jim Hendricks"                                                                                               
                      <jim at bizcomputing        To:       NYPHP Talk <talk at nyphp.org>                                                
                      inc.com>                 cc:                                                                                  
                                               Subject:  Re: [nycphp-talk] simple mysql query help                                   
                      08/06/2002 11:24                                                                                              
                      AM                                                                                                            
                      Please respond to                                                                                             
                      talk                                                                                                          
                                                                                                                                    
                                                                                                                                    



SELECT * FROM aaa WHERE aaa.ID NOT IN( SELECT ID FROM bbb )

DELETE FROM aaa WHERE aaa.ID NOT IT( SELECT ID FROM bbb )

aaa.ID and ID are the key fields which link aaa to bbb.

HTH,
Jim

----- Original Message -----
From: "cesar" <can at andrew.cmu.edu>
To: "NYPHP Talk" <talk at nyphp.org>
Sent: Tuesday, August 06, 2002 11:16 AM
Subject: [nycphp-talk] simple mysql query help


>
> i have two tables aaa and bbb
> aaa has {1 , 2}
> bbb has {1 , 3 }
>
> i want select those values in aaa that don't appear in bbb, and also
> another query that will delete those values in aaa which don't appear in
> bbb. if someone can please help me, i'm just a little stuck. i know i
> figured it out before, but right now i have no clue.
>
>  A)
> mysql> select * from aaa;
> +------+
> | a    |
> +------+
> |    1 |
> |    2 |
> +------+
>
> mysql> select * from bbb;
> +------+
> | a    |
> +------+
> |    1 |
> |    3 |
> +------+
>
> B)i understand why this does what it does, but how do i correct it?
> mysql> select aaa.*, bbb.* from aaa, bbb where aaa.a != bbb.a;
> +------+------+
> | a    | a    |
> +------+------+
> |    2 |    1 |
> |    1 |    3 |
> |    2 |    3 |
> +------+------+
>
> thnka you in advance.
>
>
>
>
>