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

Question

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?

Add Expected output

user7715598
– user7715598

2017-05-01 11:37:28 +00:00
Commented May 1, 2017 at 11:37 — user7715598
– user7715598, Commented May 1, 2017 at 11:37

Utsav · Accepted Answer · 2017-05-01 12:01:20Z

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

Andomar · Accepted Answer · 2017-05-01 11:39:31Z

1

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
        )

answered May 1, 2017 at 11:39

Andomar

239k55 gold badges387 silver badges412 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related