Query help

I'm trying to build a somewhat complicated query, well for me at least, and
I just can't figure it out.
I have a table called Inventory and a table called Auctions. The Auctions
table has a foreign key called InventoryID that links back to the Inventory
table. What I want to be able to do is to retrieve everything from the
Inventory table, and the most recent associated record in the Auctions
table. To complicate things, some Inventory items won't have any auctions
records so the columns that are returned from the auctions table need to be
null.
Here's the keys for the two tables:
Inventory Auctions
--
InventoryID AuctionID
InventoryID
An inventory item can have 0 or more auction records. The AuctionID field is
an identity field so the most recent auction record for an inventory item
can be found by: SELECT TOP 1 FROM Auctions WHERE InventoryID=XXX ORDER BY
AuctionID DESC
The result of the query would give me a single row that would contain all
the information from the inventory table and all of the information from the
most recent auction record. If no auction record was found, then these
fields would be null.
The fact that an inventory item might not have any auction records means
that I can't do a INNER JOIN, because if I do, any inventory items that
don't have at least one auction record won't get returned.
Most of my time in SQL has been spent doing pretty basic queries so I'm not
even sure where to begin to look for the best way to pull this off. If
someone could just point me in the right direction, I'll do the legwork, I
just don't know what my options are.
I appreciate any input or advice!Rachel
> table. What I want to be able to do is to retrieve everything from the
> Inventory table, and the most recent associated record in the Auctions
Since you did not provide ddl and sample data I tested it on Northwind db .
SELECT O.OrderId, Quantity from Orders O JOIN
(
SELECT OrderId,MAX(Quantity) Quantity FROM
[Order Details] GROUP BY OrderId
) OD ON OD.OrderId=O.OrderId
"Rachel Devons" <nononon@.nononon.com> wrote in message
news:ufAKr$0BFHA.3504@.TK2MSFTNGP12.phx.gbl...
> I'm trying to build a somewhat complicated query, well for me at least,
and
> I just can't figure it out.
> I have a table called Inventory and a table called Auctions. The Auctions
> table has a foreign key called InventoryID that links back to the
Inventory
> table. What I want to be able to do is to retrieve everything from the
> Inventory table, and the most recent associated record in the Auctions
> table. To complicate things, some Inventory items won't have any auctions
> records so the columns that are returned from the auctions table need to
be
> null.
> Here's the keys for the two tables:
> Inventory Auctions
> --
> InventoryID AuctionID
> InventoryID
> An inventory item can have 0 or more auction records. The AuctionID field
is
> an identity field so the most recent auction record for an inventory item
> can be found by: SELECT TOP 1 FROM Auctions WHERE InventoryID=XXX ORDER BY
> AuctionID DESC
> The result of the query would give me a single row that would contain all
> the information from the inventory table and all of the information from
the
> most recent auction record. If no auction record was found, then these
> fields would be null.
> The fact that an inventory item might not have any auction records means
> that I can't do a INNER JOIN, because if I do, any inventory items that
> don't have at least one auction record won't get returned.
> Most of my time in SQL has been spent doing pretty basic queries so I'm
not
> even sure where to begin to look for the best way to pull this off. If
> someone could just point me in the right direction, I'll do the legwork, I
> just don't know what my options are.
> I appreciate any input or advice!
>
>|||Rachel,
It should look something like this:
select
I.InventoryID, <other inventory columns>,
A.AuctionID, <other auction columns>
from Inventory as I left outer join Auctions as A
on A.InventoryID = I.InventoryID
and A.AuctionID = (
select max(AuctionID) from Auctions as Acopy
where Acopy.InventoryID = A.InventoryID
)
The left outer join will make sure each inventory item
shows up in the result (since the join has no WHERE
clause filtering out any inventory items).
The AuctionID = (select max...) will make sure that
whenever there are multiple AuctionID values for one
inventory item, you will only see the latest one.
A clustered index on Auctions(InventoryID, AuctionID)
should help if you don't already have one.
If this doesn't work out, post back and explain what
isn't working, preferably including CREATE TABLE statements
for your tables (simplified if necessary) and a few rows of
made-up by representative sample data as INSERT .. VALUES
statements to help explain what you need.
Steve Kass
Drew University
Rachel Devons wrote:

>I'm trying to build a somewhat complicated query, well for me at least, and
>I just can't figure it out.
>I have a table called Inventory and a table called Auctions. The Auctions
>table has a foreign key called InventoryID that links back to the Inventory
>table. What I want to be able to do is to retrieve everything from the
>Inventory table, and the most recent associated record in the Auctions
>table. To complicate things, some Inventory items won't have any auctions
>records so the columns that are returned from the auctions table need to be
>null.
>Here's the keys for the two tables:
>Inventory Auctions
>--
>InventoryID AuctionID
> InventoryID
>An inventory item can have 0 or more auction records. The AuctionID field i
s
>an identity field so the most recent auction record for an inventory item
>can be found by: SELECT TOP 1 FROM Auctions WHERE InventoryID=XXX ORDER BY
>AuctionID DESC
>The result of the query would give me a single row that would contain all
>the information from the inventory table and all of the information from th
e
>most recent auction record. If no auction record was found, then these
>fields would be null.
>The fact that an inventory item might not have any auction records means
>that I can't do a INNER JOIN, because if I do, any inventory items that
>don't have at least one auction record won't get returned.
>Most of my time in SQL has been spent doing pretty basic queries so I'm not
>even sure where to begin to look for the best way to pull this off. If
>someone could just point me in the right direction, I'll do the legwork, I
>just don't know what my options are.
>I appreciate any input or advice!
>
>
>|||Steve,
Thank you very much! That was much simpler than I had thought!
"Steve Kass" <skass@.drew.edu> wrote in message
news:u4%23xfZ1BFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Rachel,
> It should look something like this:
> select
> I.InventoryID, <other inventory columns>,
> A.AuctionID, <other auction columns>
> from Inventory as I left outer join Auctions as A
> on A.InventoryID = I.InventoryID
> and A.AuctionID = (
> select max(AuctionID) from Auctions as Acopy
> where Acopy.InventoryID = A.InventoryID
> )
> The left outer join will make sure each inventory item
> shows up in the result (since the join has no WHERE
> clause filtering out any inventory items).
> The AuctionID = (select max...) will make sure that
> whenever there are multiple AuctionID values for one
> inventory item, you will only see the latest one.
> A clustered index on Auctions(InventoryID, AuctionID)
> should help if you don't already have one.
> If this doesn't work out, post back and explain what
> isn't working, preferably including CREATE TABLE statements
> for your tables (simplified if necessary) and a few rows of
> made-up by representative sample data as INSERT .. VALUES
> statements to help explain what you need.
> Steve Kass
> Drew University
> Rachel Devons wrote:
>
and
Inventory
be
is
BY
the
not
I|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Could I make a sugesstion, based on guessing at your narrative and lack
of DDL?
CREATE TABLE Inventory
(item_id INTEGER NOT NULL,
.=2E.);
CREATE TABLE InventoryHistory
(item_id INTEGER NOT NULL
REFERENCES IInventory(item_id)
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME, -- null means current
item_status CHAR(3) NOT NULL
CHECK (item_status IN ('rec', 'bid', 'sld', ...)),
.=2E.
PRIMARY KEY (item_id, start_date));
This will let you build a status code system and track it over time.
An item has to be received, cataloged, bid on, sold, damaged, unsold,
etc. This lets you track the history and validate the transitions --
an item cannot be sold before it is cataloged, etc.
contain all the information from the inventory table and all of the
info=ADrmation from the
most recent auction record [sic]. If no auction record [sic] was found,
=ADthen these fields [sic] would be null. <<
Row are not records and columns are not fields. If you don't start
thinking in relational terms, you will imitate paper forms and
sequential file systems in SQL.
See what I mean about mimicking a sequential file or paper list? By
definition, an IDENTITY is a non-relational, unverifible physical
locator (like a record number on a magnetic tape) and cannot ever be a
relational key. You have a natural key with (item_id, start_date) to
track its status changes. Your query is now simple -- look for
"end_date IS NULL" and return that row.
Most of the time, a compicated query for a simple basic fact comes from
a bad data model.