Subquery joins

I need to create a query that shows all of the products that were shipped via the same method as OrderID 10248.

SELECT     dbo.Products.ProductID
FROM         dbo.Products JOIN
                              orders on      ShipVia = (select distinct ShipVia AS sv from Orders where OrderID = 10248) INNER JOIN
                              dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID INNER JOIN
                      dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID

This is how the columns are related:

-Order Details                  -Orders                   -Prodcuts
    OrderID                            OrderID                    ProductID
    ProductID                         ShipVia



usky1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Om PrakashCommented:
please check the code
select ProductID from OrderDetails  od
  join Orders o on o.orderid = od.orderid 
WHERE ShipVia in (select distinct ShipVia AS sv from Orders where OrderID = 10248)

Open in new window

0
phyderousCommented:
select ProductID
from OrderDetails inner join Orders on OrderDetails.orderid = Orders.orderid  
WHERE ShipVia = 10248

or

select ProductID
from OrderDetails,Orders
WHERE ShipVia = 10248
and OrderDetails.orderid = Orders.orderid  



0
usky1Author Commented:
om_prakash_p:

When I do the following query (this is to test exactly how many rows):

select orderid from Orders
where ShipVia = (select ShipVia from Orders where OrderID = 10248)

I get 255 rows

But when I run your query I get 645 rows

phyderous:
Neither of your queries worked.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

phyderousCommented:
ok

select roductID from [Order Details] where OrderID in (select o.OrderID from Orders as o where SipVia in (select SipVia from Orders as oa where oa.OrderID = 10248)

0
phyderousCommented:

select ProductID from [Order Details] where OrderID in (select o.OrderID from Orders as o where SipVia in (select SipVia from Orders as oa where oa.OrderID = 10248)

Open in new window

0
phyderousCommented:
Sorry one small correction

select ProductID
from [Order Details] where OrderID in
                    (select o.OrderID
                     from Orders as o
                    where o.SipVia in
                                 (select SipVia
                                  from Orders as oa
                                  where oa.OrderID = 10248)
)
0
usky1Author Commented:
phyderous:

I am still getting 645 rows from the query.

From what I see it looks like it is randomly adding the shipvia value to the products table. Here is a partial result; The shipvia value for OrderID 10248 is 3. So it is returning all the shipvia values and repeating rows.

ProductID   ShipVia
----------- -----------
11          1
42          1
72          1
11          1
42          1
72          1
11          1
42          1
72          1
11          1
42          1
72          1
11          1
42          1
72          1
11          1
42          1
72          1
11          3
42          3
72          3
11          3
42          3
72          3
11          3
42          3
72          3
0
ThomasianCommented:
I believe om_prakash_p's query should give the right list of product_id. The reason it is returning more records is probably because product_id is not unique in the orderdetails table, thus it is returning duplicate product_ids.

Try to add DISTINCT in the query

i.e.

select distinct ProductID from OrderDetails  od
0
usky1Author Commented:
Thomasian:

I tried distinct before and it strips all the duplicate productid's. I need to see all the productid's that where shipped with the shipvia value for OrderID 10248.
So the shipvia value is 3 and I need all the productid's that shipped via 3.

0
phyderousCommented:
I agree with Thomasian,

I tried a diffrent solution just to make sure we are getting the same result as in the join

you can use either

select Distinct ProductID
from [Order Details] where OrderID in
                    (select o.OrderID
                     from Orders as o
                    where o.SipVia in
                                 (select SipVia
                                  from Orders as oa
                                  where oa.OrderID = 10248)
)

or om_prakash_p's query with a change

select Distinct ProductID from OrderDetails  od
  join Orders o on o.orderid = od.orderid  
WHERE ShipVia in (select distinct ShipVia AS sv from Orders where OrderID = 10248)
0
phyderousCommented:
ok now it is clear

select Distinct ProductID, ShipVia from OrderDetails  od
  join Orders o on o.orderid = od.orderid  
WHERE ShipVia in (select distinct ShipVia AS sv from Orders where OrderID = 10248)

0
ThomasianCommented:
In your post http:#a28450567 , you compared the number of records returned by your query and om_prakash_p's. But, you must not expect that the number of records returned will be the same because an order can involve multiple products and different orders can have the same products.

Are you sure that you are not getting the right results? Can you show a sample record which you want returned but was not?
0
phyderousCommented:
Can you please run the following qurey and send the results

select ShipVia, count(*)
from orders
where OrderID = 10248
group by shipvia


0
phyderousCommented:
and also please send

select orderid,count(*) from [Order Details] where OrderID = 10248 group by OrderID
0
usky1Author Commented:
Hi all,

Thanks for your help on this problem. I just wanted to let you know that I was put on an emergency task at work. It might be a day or two until I can get back.
0
usky1Author Commented:
phyderous:

The first query returned, ShipVia = 3 and count = 1
The second query returned,  OrderID = 10248 and count = 3
0
usky1Author Commented:
Thomasian:

This is a sample of what I need back,

ProductID  |    ShipVia

103           3
103           3
103            3
127            3
156            3
156            3
243            3

It should have 255 rows.  There are repeats of the ID because that ID had different products ship.
0
usky1Author Commented:
Thomasian:

Please forget that last comment. I was thinking about something else.
0
phyderousCommented:
the first answer you got seems to be right,

if you want to make sure with the 645 result you should run

select ProductID from [Order Details] od
  join Orders o on o.orderid = od.orderid  
WHERE o.shipvia = 3

that would be true since you stated that The first query returned, ShipVia = 3 and count = 1


and your query should be
select ProductID from [Order Details] od 
  join Orders o on o.orderid = od.orderid  
WHERE ShipVia in (select distinct ShipVia AS sv from Orders where OrderID = 10248)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
usky1Author Commented:
phyderous:

That works great.  Thanks for all your help on this. I'm still learning subqueries.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.