help with a sql query

Question

(newbie, help if you can please)

hi, 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'

Answer 1

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.

Answer 2

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'

Answer 3

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?

Answer 4

How about doing with SubQuery method?

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