Querying SQLite

I have a table called ProductClassification with two columns 'ProductID, ClassificationID'

ProductID, ClassificationID
10408, 1749
10408, 1750

A ProductID could have multiple ClassificationID. How would I extract a single product if I query multiple different ClassificationID.

for example: the below doesn't work

SELECT Product.ID, Product.Name FROM
Product, ProductClassificat开发者_JAVA百科ion WHERE
ProductClassification.ClassificationID = '1750' AND
ProductClassification.ClassificationID = '1749'

• The WHERE clause is checked on a record by record basis.
• In a single record, the ClassificationID can't be 1750 AND 1749.
• Changing this to 1750 OR 1749 allows eahc indivudal record to filter through.
• Then you need to check that for any One Product.ID both records came through.

SELECT
  Product.ID,
  Product.Name
FROM
  Product
INNER JOIN
  ProductClassification 
    ON Product.ID = ProductClassification.ProductID
WHERE
  ProductClassification.ClassificationID = '1750' OR
  ProductClassification.ClassificationID = '1749'
GROUP BY
  Product.ID,
  Product.Name
HAVING
  COUNT(DISTINCT ProductClassification.ClassificationID) = 2

You want an OR or an IN clause (not sure if SQLLite supports or not).

Your query as written is looking for two values in the same field for the SAME RECORD, which is not possible.

Try:

SELECT Product.ID, Product.Name FROM
Product, ProductClassification WHERE
ProductClassification.ClassificationID = '1750' OR
ProductClassification.ClassificationID = '1749'

OR

SELECT Product.ID, Product.Name FROM
Product, ProductClassification WHERE
ProductClassification.ClassificationID IN ('1750','1749')

type this , you miss the "inner join " part for example:

SELECT Product.ID, Product.Name FROM Product inner join ProductClassification  on Product.ClassificationID  = ProductClassification.ClassificationID WHERE
ProductClassification.ClassificationID = '1750' or
ProductClassification.ClassificationID = '1749'

You seem to be new to SQL. It's heplful to walk through it. I would suggest putting in these SQL statements to get a feel for what's going on.

SELECT * FROM Product, ProductClassification

At this point, you will be selecting every possible row combination from both tables. Now you want to start the filter. For starters, you need to get your product classifications. You should use an "OR" here instead of AND. No ProductClassification.ClassificationID will be BOTH 1750 AND 1749.

SELECT * FROM Product, ProductClassification WHERE
ProductClassification.ClassificationID = '1750' OR
ProductClassification.ClassificationID = '1749'

At this point, you have filtered down the ProductClassification.ClassificationID, but you need to describe that you're only interested in rows where the ProductClassification.ProductID and the Product.ID are the same.

SELECT * FROM Product, ProductClassification WHERE
(ProductClassification.ClassificationID = '1750' OR
ProductClassification.ClassificationID = '1749') AND
ProductClassification.ProductID = Product.ID

That'll give you an individual row for each possible combination on that filter. With your example, you should have two rows - one with a ClassificationID of 1750 and one with a ClassificationID of 1749. Since you're only interested in the Product.ID and Product.Name, you should only return those two fields:

SELECT Product.ID, Product.Name FROM Product, ProductClassification WHERE
(ProductClassification.ClassificationID = '1750' OR
ProductClassification.ClassificationID = '1749') AND
ProductClassification.ProductID = Product.ID

And presumably you only want unique values, so you can use the "DISTINCT" operator which will filter down your return to unique values:

SELECT DISTINCT Product.ID, Product.Name FROM Product, ProductClassification WHERE
(ProductClassification.ClassificationID = '1750' OR
ProductClassification.ClassificationID = '1749') AND
ProductClassification.ProductID = Product.ID

There are much better ways to handle this query using JOIN statements, but that's a good start.