?
Solved

Subquery joins

Posted on 2010-03-24
20
Medium Priority
?
557 Views
Last Modified: 2012-05-09
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



0
Comment
Question by:usky1
  • 9
  • 8
  • 2
  • +1
20 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 28445295
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
 
LVL 5

Expert Comment

by:phyderous
ID: 28448868
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
 

Author Comment

by:usky1
ID: 28450567
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 5

Expert Comment

by:phyderous
ID: 28451212
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
 
LVL 5

Expert Comment

by:phyderous
ID: 28451249

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
 
LVL 5

Expert Comment

by:phyderous
ID: 28451408
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
 

Author Comment

by:usky1
ID: 28458506
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
 
LVL 22

Expert Comment

by:Thomasian
ID: 28459722
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
 

Author Comment

by:usky1
ID: 28460344
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
 
LVL 5

Expert Comment

by:phyderous
ID: 28460512
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
 
LVL 5

Expert Comment

by:phyderous
ID: 28460653
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
 
LVL 22

Expert Comment

by:Thomasian
ID: 28460897
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
 
LVL 5

Expert Comment

by:phyderous
ID: 28461246
Can you please run the following qurey and send the results

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


0
 
LVL 5

Expert Comment

by:phyderous
ID: 28462097
and also please send

select orderid,count(*) from [Order Details] where OrderID = 10248 group by OrderID
0
 

Author Comment

by:usky1
ID: 28466495
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
 

Author Comment

by:usky1
ID: 28700121
phyderous:

The first query returned, ShipVia = 3 and count = 1
The second query returned,  OrderID = 10248 and count = 3
0
 

Author Comment

by:usky1
ID: 28700502
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
 

Author Comment

by:usky1
ID: 28704280
Thomasian:

Please forget that last comment. I was thinking about something else.
0
 
LVL 5

Accepted Solution

by:
phyderous earned 2000 total points
ID: 28762328
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
 

Author Closing Comment

by:usky1
ID: 31706515
phyderous:

That works great.  Thanks for all your help on this. I'm still learning subqueries.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question