usky1
asked on
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
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
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
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
ASKER
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.
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.
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)
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)
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)
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)
)
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)
)
ASKER
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
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
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
Try to add DISTINCT in the query
i.e.
select distinct ProductID from OrderDetails od
ASKER
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.
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.
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)
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)
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)
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)
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?
Are you sure that you are not getting the right results? Can you show a sample record which you want returned but was not?
Can you please run the following qurey and send the results
select ShipVia, count(*)
from orders
where OrderID = 10248
group by shipvia
select ShipVia, count(*)
from orders
where OrderID = 10248
group by shipvia
and also please send
select orderid,count(*) from [Order Details] where OrderID = 10248 group by OrderID
select orderid,count(*) from [Order Details] where OrderID = 10248 group by OrderID
ASKER
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.
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.
ASKER
phyderous:
The first query returned, ShipVia = 3 and count = 1
The second query returned, OrderID = 10248 and count = 3
The first query returned, ShipVia = 3 and count = 1
The second query returned, OrderID = 10248 and count = 3
ASKER
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.
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.
ASKER
Thomasian:
Please forget that last comment. I was thinking about something else.
Please forget that last comment. I was thinking about something else.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
phyderous:
That works great. Thanks for all your help on this. I'm still learning subqueries.
That works great. Thanks for all your help on this. I'm still learning subqueries.
Open in new window