SQL Query help ?? [SOLVED]

[ilan]

Hi everyone,

i need help with creating a query.

i have 2 tables.
Category and products.

i have about 20 categories and each has an id. Now i have few products and every product belongs to a specific category with the category id.

i want to list all categories ids that has at least 1 product with the same cat id.

how can i do it?

thanx

 

[RodM]

Hi,

I'm not 100% sure if I understood, but...

SELECT DISTINCT CATEGORY_ID FROM PRODUCTS

would work?

 

[ilan]

Hi,

I'm not 100% sure if I understood, but...

SELECT DISTINCT CATEGORY_ID FROM PRODUCTS

would work?

Yes, this would be a solution that i have already figured out but what i wanted is to use 2 tables and make the query on table 1 depending on query on table 2.

i think i will need to use join here, right?

 

[Deleted member 103]

Yes, this would be a solution that i have already figured out but what i wanted is to use 2 tables and make the query on table 1 depending on query on table 2.

i think i will need to use join here, right?

you can use join or a second "SELECT DISTINCT ...", as it is easier for you.

 

[josejad]

Try with

SELECT DISTINCT categories.id
FROM categories INNER JOIN
  product ON categories.id = product.id_category

 

[JoseAlfredo]

SELECT b.IdCategory FROM PRODUCTS a
    INNER JOIN Categories b
    ON b.CategoryId = a.IdCategory

 

[ilan]

Try with

SELECT DISTINCT categories.id
FROM categories INNER JOIN
  product ON categories.id = product.id_category

thank you very much, this is what i was looking for ? ? ?

SELECT b.IdCategory FROM PRODUCTS a
    INNER JOIN Categories b
    ON b.CategoryId = a.IdCategory

thanks, this will return duplicated results. adding distinct as @José J. Aguilar recommended will do the trick.

 

[TILogistic]

Normal SQL

 SELECT DISTINCT categories.id, categories.name
  FROM categories, products
 WHERE products.productId = ?  (Id Cat)
   AND categories.id = products.Idcategory

Count

SELECT categories.id, categories.name, Count(*)
  FROM categories, products
 WHERE products.productId = ?  (Id Cat)
   AND categories.id = products.Idcategory
 GROUP BY categories.id

 

[ilan]

Normal SQL

 SELECT DICTINTC categories.id, categories.name
  FROM categories, products
 WHERE products.productId = ?  (Id Cat)
   AND categories.id = products.Idcategory

Count

SELECT categories.id, categories.name, Count(*)
  FROM categories, products
 WHERE products.productId = ?  (Id Cat)
   AND categories.id = products.Idcategory
 GROUP BY categories.id

Thanx, thats very helpful ?

 

[TILogistic]

SQL Normal (ALL Data Base)

Example SQLite demo DB. Chinook