Simple join

9 Mar 2006 6:56 PM

HockeyFan

I'm not very experienced at joins. Wondering how to get a query that will do
what I need, and I know I need a join.

I have an Order table, and I have an OrderStatus table.
The Order table has an int field that is StatusID, which refers to the
OrderStatus table, primary key, which is StatusID.

I need to get everything from Order, where OwnerCompany= anumberigiveit'
and OrderStatus.Description='New'

So I need to be able to get the records from Order, where when using
StatusID to check OrderStatus.Description='New'

What's the SQL for this? I know it's a simple join.

9 Mar 2006 7:01 PM

HockeyFan

So is the solution:

Select Count(*)
From T_Order O JOIN T_OrderStatus OS
ON O.OrderStatusID = OS.OrderStatusID
WHERE O.OwnerCompany = 7
AND OS.Description='New'

Is this it, or is there a better way?

Show quote

"HockeyFan" wrote:

> I'm not very experienced at joins. Wondering how to get a query that will do
> what I need, and I know I need a join.
>
> I have an Order table, and I have an OrderStatus table.
> The Order table has an int field that is StatusID, which refers to the
> OrderStatus table, primary key, which is StatusID.
>
> I need to get everything from Order, where OwnerCompany= anumberigiveit'
> and OrderStatus.Description='New'
>
>
> So I need to be able to get the records from Order, where when using
> StatusID to check OrderStatus.Description='New'
>
> What's the SQL for this? I know it's a simple join.
>

9 Mar 2006 7:05 PM

Robert Ellis

Hello,

    SELECT ord.*
    FROM     Order ord INNER JOIN OrderStatus ors ON (ord.StatusID =
ors.StatusID)
    WHERE (ord.OwnerCompany=@int) AND (ors.Description=@varchar)

I am having to do a lot of guesswork here. It's important to post DDL
scripts if you want an accurate answer to your questions. You also fail to
specify the exact join behaviour you are looking for. You need to read-up on
the basics of JOINS (FULL, INNER, OUTER, LEFT, RIGHT) otherwise you will
struggle badly to get the results you want out of SQL.

Thanks,

Robert

Show quote

"HockeyFan" <Hockey***@discussions.microsoft.com> wrote in message
news:845786C2-26E6-49F1-83B7-52D65A69D2D5@microsoft.com...
> I'm not very experienced at joins. Wondering how to get a query that will
> do
> what I need, and I know I need a join.
>
> I have an Order table, and I have an OrderStatus table.
> The Order table has an int field that is StatusID, which refers to the
> OrderStatus table, primary key, which is StatusID.
>
> I need to get everything from Order, where OwnerCompany= anumberigiveit'
> and OrderStatus.Description='New'
>
>
> So I need to be able to get the records from Order, where when using
> StatusID to check OrderStatus.Description='New'
>
> What's the SQL for this? I know it's a simple join.
>

9 Mar 2006 7:08 PM

Robert Ellis

Correction:

SELECT ord.*
FROM [Order] ord INNER JOIN OrderStatus ors ON (ord.StatusID =
ors.StatusID)

By the way: Is your table really called [Order] ? That's not good, cos it's
a reserved keyword.

Robert.

Show quote

"HockeyFan" <Hockey***@discussions.microsoft.com> wrote in message
news:845786C2-26E6-49F1-83B7-52D65A69D2D5@microsoft.com...
> I'm not very experienced at joins. Wondering how to get a query that will
> do
> what I need, and I know I need a join.
>
> I have an Order table, and I have an OrderStatus table.
> The Order table has an int field that is StatusID, which refers to the
> OrderStatus table, primary key, which is StatusID.
>
> I need to get everything from Order, where OwnerCompany= anumberigiveit'
> and OrderStatus.Description='New'
>
>
> So I need to be able to get the records from Order, where when using
> StatusID to check OrderStatus.Description='New'
>
> What's the SQL for this? I know it's a simple join.
>