Sk Asraf Sk Asraf - 5 months ago 10
SQL Question

how can select unique row using where/having cluse and compare with another table

i cant understand how can take unique column (remove duplication) from a table
which compare with another table data.

in my case

i have two table

enter image description here

i want to get unique rows from tblproduct after compireing with tblviewer as
[in table viewer first taking viewerid after that taking productid in viewer table afterthat compire with tblproduct.

actualy like that

if i take vieweris=123 two row productid select 12001&11001 after that this tblproduct productid and finaly taking the row from tblproduct which maching.

select *
from tblproduct
where productid =
(
select distinct(productid)
from tblviewer
where viewerid = 123
)

Answer

There are a few ways to do this. You can do a standard INNER JOIN to the table to filter the results:

Select  Distinct P.*
From    tblProduct  P
Join    tblViewer   V   On  V.ProductId = P.ProductId
Where   V.ViewerId = 123

Alternatively, you could use EXISTS as well - this eliminates the need to use a DISTINCT altogether:

Select  *
From    tblProduct  P
Where Exists
(
    Select  *
    From    tblViewer   V
    Where   V.ProductId = P.ProductId
    And     V.ViewerId = 123
)

Or, you could also use an IN, as suggested by the other answers:

Select  * 
From    tblProduct 
Where   ProductId In 
(
    Select  ProductId
    From    tblViewer 
    Where   ViewerId = 123
)
Comments