SQL Query help ?? [SOLVED]

ilan · Dec 12, 2021

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 · Dec 12, 2021

Hi,

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

SELECT DISTINCT CATEGORY_ID FROM PRODUCTS

would work?

ilan · Dec 12, 2021

RMMIRON said:
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 · Dec 12, 2021

ilan said:
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 · Dec 12, 2021

Try with

B4X:

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

JoseAlfredo · Dec 12, 2021

B4X:

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

ilan · Dec 12, 2021

José J. Aguilar said:
Try with

B4X:

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 ? ? ?

JoseAlfredo said:

B4X:

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 · Dec 12, 2021

Normal SQL

SQL:

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

Count

SQL:

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

ilan · Dec 12, 2021

oparra said:

Normal SQL

SQL:

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

Count

SQL:

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 · Dec 12, 2021

SQL Normal (ALL Data Base)

Example SQLite demo DB. Chinook

SQL Query help ?? [SOLVED]

ilan

Expert

RodM

Member

ilan

Expert

Deleted member 103

Guest

josejad

Expert

JoseAlfredo

Member

ilan

Expert

TILogistic

Expert

ilan

Expert

TILogistic

Expert

SQL Query help ?? [SOLVED]

Expert

Member

Expert

Deleted member 103

Guest

Expert

Member

Expert

Expert

Expert

Expert

Privacy & Transparency

Privacy & Transparency