CODEWITHSUNDEEP
sqlsql-serverselect
Ziil
Ziil

Reputation: 351

SQL Server : select values from multiple rows when one row met condition

I have a table products, one specific product may contain different models, model 0 represents basic model: 

Select * 
From products 
Where product_number = 12345;

Result: 

id  made_by product_number  model     
----------------------------------
122 U123    12345           0  
123 U123    12345           1  
124 U552    12345           2

Now I would like to find only these products where basic model (model=0) made_by value differs in some model?

In this example I am definitely interested in to have this product 12345 in results.

On thing what came to my mind is to select distinct and count, but maybe there are better ways?

Upvotes: 1

Views: 1744

Answers (2)

Utsav
Utsav

Reputation: 8093

Exists is a better way to do it, but you can do it with self join also like below

Resterster sample

select t1.*
    from products t1
inner join 
    products t2
on 
    t1.product_number=t2.product_number
    and t1.made_by<>t2.made_by
    and t1.model<>0
    and t2.model=0;

Sample Data

select * from products;

+-----+---------+----------------+-------+
| id  | made_by | product_number | model |
+-----+---------+----------------+-------+
| 122 | U123    |          12345 |     0 |
| 123 | U123    |          12345 |     1 |
| 124 | U552    |          12345 |     2 |
| 125 | U5      |          12346 |     0 |
| 126 | U5      |          12346 |     1 |
| 127 | U6      |          12347 |     0 |
| 128 | U7      |          12347 |     1 |
+-----+---------+----------------+-------+

Output

+-----+---------+----------------+-------+
| id  | made_by | product_number | model |
+-----+---------+----------------+-------+
| 124 | U552    |          12345 |     2 |
| 128 | U7      |          12347 |     1 |
+-----+---------+----------------+-------+

Upvotes: 1

Andomar
Andomar

Reputation: 238086

You could use an exists subquery to check for a different made_by for the same product_number:

select  product_number
from    YourTable yt1
where   yt1.model = 0
        and exists
        (
        select  *
        from    YourTable yt2
        where   yt1.product_number = yt2.product_number
                and yt2.made_by <> yt1.made_by
        )

Upvotes: 1

Related Questions