0

I could use some help writing a SQL query. I'm trying to display some data from one table but the data I need depends on a value from a different table. I'm pretty new to this so I will try to explain this the best I can:

I have an Orders table with a ShipCity and OrderId columns. I would like to get the OrderId value from Orders where ShipCity = Caracas. using those distinct OrderId values, I would like to query a different table called Order Details where [Order Details].[OrderId] = [Orders].[OrderId] (= to 'Caracas').

I hope that made sense. where I'm getting stuck is I'm sure that I will either need to create some variables or a temporary table to store these values and I don't have any experience with these things yet. I'd appreciate any help. also, these are tables in the Northwind sample database if that helps. below is a dummy sample of what I'm trying to do.

Select OrderId
FROM [Orders]
WHERE ShipCity = 'Caracas'

Select OrderId
FROM [Order Details]
WHERE OrderId = (Orders.ShipCity = 'Caracas')

here's another way of looking at it:

SELECT OrderId
FROM [Order Details]
WHERE OrderId = [Orders].ShipCity = 'Caracas'
Improve this question

4 Answers 4

Reset to default
3

You want to use an INNER JOIN

SELECT [Order Details].*
FROM [Order Details]
INNER JOIN [Orders] ON [Orders].OrderId = [Order Details].OrderId
WHERE [Orders].ShipCity = 'Caracas'

More information about joins can be found in the Wikipedia entry or here.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Well now I've just seen about everything :) Thanks!
3

you use a JOIN clause to combine data from two or more tables. Something like this, although you should double check the syntax

select * 
from [Orders] o
join [Order Details] od on o.orderid = od.orderid
where o.shipcity = 'Caracas'
Improve this answer

Comments

1

You need to join the two tables:

SELECT DISTINCT o.OrderId
FROM Orders o INNER JOIN [Order Details] od ON o.OrderId = od.OrderId
WHERE o.ShipCity = 'Caracas'

...but why do you need the Order Details table in the query?

Improve this answer

Comments

0

How about doing with SubQuery method?

SELECT OrderId
FROM [Order Details]
WHERE (OrderId IN SELECT OrderId FROM Orders WHERE ShipCity = 'Caracas')
Improve this answer

4 Comments

Why would you use a subquery when a join will suffice? Also, your left parenthesis in the WHERE statement should be to the right of IN.
As you know, using join syntax make SQL server to be over-loaded. Because, it crawls both of table at the same time. That's why I use subquery.
Overloaded? You get a table scan if there's no index to take advantage of, or worse--your data needs to be massaged in order to join to another table.
What I believe is join syntax will scan to the whole records of the first table and second table at the same time. Subquery isn't like that. It will scan to the last clause first, then the main clause. Correct what I said is wrong.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.