CODEWITHSUNDEEP
sqloracle-database
sasori
sasori

Reputation: 5445

how to select only 1 unique data when joining two tables, if the table structure is one to many?

i am using an oracle database, i have two tables.

table A primary key = productid

table B references productid of table A

primary key = imageid

flow:

each product should have 4 images stored in the table B (mandatory)

problem:

there are some products that has only 2 or sometimes 3 or sometimes 1 image only despite of the fact that the 4 images rule is mandatory based from code level.

Question:

how to count unique number of products that has images in table b?Because, if I do

select count(*) from tableA join tableB on tableA.productid = tableB.productid

the result is double, because it's a one to many...as in , one product has many images. So let's say productID = 12345 has 4 images in table B, once I ran my query, the result is 4 , when i want to only get 1...so how?

Upvotes: 0

Views: 99

Answers (3)

Jason Heo
Jason Heo

Reputation: 10236

SELECT Count(DISTINCT TableA.productid)
FROM   TableA 
JOIN   TableB ON TableA.productid = TableB.productid;

Upvotes: 2

realnumber3012
realnumber3012

Reputation: 1062

SELECT COUNT(*) FROM
(    
select A.productId from tableA A join tableB B on A.productid = B.productid
    GROUP BY A.productId
    HAVING COUNT(B.imageId) > 1 ) T

Upvotes: 0

Dijkgraaf
Dijkgraaf

Reputation: 11527

Do a sub query with where exists

select count(*) from tableA 
where exists (select 1 from tableB where tableA.productid = tableB.productid)

Upvotes: 1

Related Questions