2 Replies Latest reply on Aug 8, 2007 2:35 AM by Sankalan

    list of non categories

    JohnGree Level 1
      Hi i have 2 tables, ProductsTable PT and CatToProducts CTP

      i want a list productID from the ProductsTable that do not have a entry in the CatToProducts table

      i have tried

      WHERE PT.Products_ID <> CTP.ProductID99

      but that gives me wrong results?
        • 1. Re: list of non categories
          Iceborer Level 1
          Of course, syntax is database-dependent, but something like this should work

          select PT.productID
          from ProductsTable PT left outer join CatToProducts CTP on CTP.ProductID99 = PT.Products_ID
          where CTP.ProductID99 is null

          The outer join causes the linked table to have null values in all fields for rows which do not match the driving table.
          • 2. Re: list of non categories
            Sankalan Level 1
            Try something like:

            SELECT PT.Products_ID FROM ProductsTable PT WHERE PT.Products_ID not in (SELECT CTP.ProductID99 FROM CatToProducts CTP)