Gossamer Forum: Products: Gossamer Links: Development, Plugins and Globals: Using INNER JOIN to duplicate CatLinks (rgbworld)

I need to adjust the INNER JOIN below so I can select links within a certain category.
I really want "at category and below", but will settle for in category for starts.

Here's my perfectly working Distance SELECT.

Code:
         my $code = "  
        SELECT ".$DB->prefix."Links.*, o.*, ".$DB->prefix."ZCS_Countries.ZCS_Country,  
            (".$earth_radius." * (2 * ASIN(SQRT(  
                POWER(SIN(((z.ZCS_Latitude-o.ZCS_Latitude)*0.017453293)/2),2) +  
                COS(z.ZCS_Latitude*0.017453293) *  
                COS(o.ZCS_Latitude*0.017453293) *  
                POWER(SIN(((z.ZCS_Longitude-o.ZCS_Longitude)*0.017453293)/2),2)  
            )))) AS Distance  

        FROM ".$DB->prefix."ZipCodes z,  
            ".$DB->prefix."ZipCodes o  

        INNER JOIN ".$DB->prefix."Links ON o.ZCS_ZipCode = ".$DB->prefix."Links.".$opts->{'links_zipcode_col'}."          
        INNER JOIN ".$DB->prefix."ZCS_Countries ON o.ZCS_CC = ".$DB->prefix."ZCS_Countries.ZCS_CC              

        WHERE z.ZCS_ZipCode = ".$query."   
        GROUP BY Distance SORT_ORDER, Distance having Distance <= ".$dist  
        ;  

        $code =~ s/SORT_ORDER/$sort_order/;   
        my $zipdb  = $DB->table('ZipCodes') || print $GT::SQL::error;  
        my $zip_sth = $zipdb->prepare($code) || print "Unable to PREPARE query - ".$GT::SQL::error;  
        $zip_sth->execute () || print "Unable to EXECUTE query - ".$GT::SQL::error;

I need help with the code colored red. I have simplified the JOIN's by removing the db prefix for easier reading.

Code:
         my $code = "  
        SELECT Links.*, o.*, ZCS_Countries.ZCS_Country, CatLinks.CategoryID,
            (".$earth_radius." * (2 * ASIN(SQRT(    
POWER(SIN(((z.ZCS_Latitude-o.ZCS_Latitude)*0.017453293)/2),2) +  
                COS(z.ZCS_Latitude*0.017453293) *  
                COS(o.ZCS_Latitude*0.017453293) *     
POWER(SIN(((z.ZCS_Longitude-o.ZCS_Longitude)*0.017453293)/2),2)  
            )))) AS Distance  

        FROM ZipCodes z,  
            ZipCodes o  

        INNER JOIN Links ON o.ZCS_ZipCode = Links.".$opts->{'links_zipcode_col'}."  
        INNER JOIN ZCS_Countries ON o.ZCS_CC = ZCS_Countries.ZCS_CC     

INNER JOIN CatLinks ON Links.ID = CatLinks.LinkID

        WHERE z.ZCS_ZipCode = ".$query." AND CatLinks.CategoryID = ".$catid."
        GROUP BY Distance SORT_ORDER, Distance having Distance <= ".$dist  
        ;

I am not sure if I add the AND CatLinks.CategoryID = ".$catid." to the WHERE clause, or if I just do the JOIN so that each link returned in the results has a catid joined to it. Then while looping through results just qualify for catid.

[edit]
I guess I should ask a question...
How do I imitate CatLinks via an INNER JOIN?

Hope it makes sense, also hope someone can help.

Thanks,
Chris
RGB World, Inc. - Software & Web Development.
rgbworld.com

Subject	Author	Views	Date
Using INNER JOIN to duplicate CatLinks	rgbworld	3103	Jul 6, 2006, 11:16 AM

Re: [rgbworld] Using INNER JOIN to duplicate CatLinks	pugdog	2973	Jul 20, 2006, 9:44 PM

Re: [pugdog] Using INNER JOIN to duplicate CatLinks	rgbworld	2938	Jul 21, 2006, 11:18 AM

Re: [pugdog] Using INNER JOIN to duplicate CatLinks	rgbworld	2936	Jul 21, 2006, 11:39 AM

Using INNER JOIN to duplicate CatLinks

Last edited by: