Join query results

mrgrammar

I have two tables from which I need every row from one table and only those rows equal from the other. Here is the query that I'm using.

$getlist = "SELECT sponsorlistings.tfnlistingid, sponsorlistings.listingtitle, sponsorlistings.listingurl, sponsorlistings.listingdesc, sponsorlistings.bannerurl, sponsorlistings.tfnsiteid, sponsorlistings.sponsorid, sponsorlistings.listingapprv, sponsorlistings.cattypeid, categories.cattypedesc FROM categories LEFT JOIN sponsorlistings ON categories.cattypeid = sponsorlistings.cattypeid WHERE sponsorid = ".$asponsorid." ORDER BY cattypedesc";

This returns the records from both tables where the cattypeid field is equal.

I need a list of ALL cattypeid's and ONLY the records from sponsorlistings where the cattypeid fields are equal.

What needs to be done to the query to achieve this?

Mr. Grammar

LordShryku

Instead of selecting the cattypeid from sponsorlistings, select it from categories. You could really use some structure to this query also. Abbreviations and newlines...

$getlist = "SELECT s.tfnlistingid, 
                   s.listingtitle, 
                   s.listingurl, 
                   s.listingdesc, 
                   s.bannerurl, 
                   s.tfnsiteid, 
                   s.sponsorid, 
                   s.listingapprv, 
                   c.cattypeid, 
                   c.cattypedesc 
              FROM categories AS c 
         LEFT JOIN sponsorlistings AS s
                ON c.cattypeid = s.cattypeid 
             WHERE s.sponsorid = ".$asponsorid." 
          ORDER BY c.cattypedesc";

mrgrammar

Thanks for your reply. I'm new to php (4 months), but I'm learning quickly with occasional help from this board.

It cleans up the query. 🙂 However, the results still only gives me a list of categories where cattypeid is present in both tables. I need the results to list all categories (there's about 60 of them).

Can this be done in just one query?

LordShryku

Not with the WHERE clause in there. That's narrowing it down to the one thing from the one table. So the categories that don't fall under that won't show.