Help with subquery
-
Hello,
I need help buiding a query:
I have Multiple single items with different statuses (one item can have several diff. statuses).
The items and statuses are in different tables.
I just want to have a recordset with each unique item and latest status. Here's what I have now.
1st Query:
//Get all individual items based on ItemField
"SELECT DISTINCT Table1.ItemField FROM Table1 WHERE
Table1.CustomerID=" + CustomerID
Loop through each record to get latest status:
2nd Query:
"SELECT TOP 1 * FROM Table1 INNER JOIN Table2 ON Table1.DataID = Table2.DataID INNER JOIN
Table3 ON Table2.Status = Table3.StatusID
WHERE Table1.CustomerID= CustomerID AND Table1.Item= " + oDR["ItemField"] (Table1.ItemField from first dataset) + " ORDER BY Table2.StatusChangeDate DESC";
I want to be able to put these 2 queries together so that I don't have to use more resources looping through the recordset.
Thanks,
Jen
-
It's quite easy to do, but I'm not sure of what you want to do.
Can you post the table definition, some sample data and what you want the select statement to produce as output?
-
Am I understanding you correctly?
use northwind
select * from orders t1
where t1.orderdate=
(select max(orderdate) from orders t2
where t1.customerid=t2.customerid)
order by t1.customerid
select * from orders t1 inner join
(select customerid, max(orderdate) as maxdate from orders group by customerid) t2
on t1.customerid = t2.customerid
and t1.orderdate = t2.maxdate
order by t1.customerid
select t1.*
from orders t1
where t1.orderdate in
(
select top 1 t2.orderdate
from orders t2
where t2.customerid = t1.customerid
order by t2.orderdate desc
)
order by t1.customerid
I wouldn't use the TOP method. The others are better and faster.
Now, if that doesn't go your direction, I echo Remi: Please post DDL, sample data and required output.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Unlike the Northwind example, the CustomerID is only in Table 1. The two tables
are connected by a DataID.
Table 1 has the ItemField, CustomerID, DataID
Table 2 has the StatusChangeDate, DataID, Status
Table 3 has the StatusID, StatusDescription
Jennifer
-
Continued...
Most of the Information that I want to retrieve about the Item is in Items (Table 1)
ItemDetails (Table 2) has some additional information about the item, including the status. Each time the status changes, there is a new StatusChangeDate, creating duplicate records. I need to get the latest status based upon the StatusChangeDate for that item.
ItemStatus (Table3) is where I get the status description, based upon the status.
-
I think Frank pretty much gave you everything you need to work this out. Anyway...
Select t1.*, t3.status_description
From t1
Join t2
on t2.DataId = t1.DataId
And t2.StatusChangeDate =
(Select Max(StatusChangeDate)
From t2
Where t2.DataId = t1.DataId)
Join t3
On t3.StatusId = t2.Status
-
The problem is that the DataID is not unique, so the only distinct item is the ItemField in my example. This field is only in the Items table, not ItemDetails.
Thanks,
Jennifer
-
The problem is with the duplicates. The query that Ron gave worked, but there are
still duplicate ItemFields, but unique DataIDs.
-
At this point I'll have to quote from Remi's post.
"It's quite easy to do, but I'm not sure of what you want to do.
Can you post the table definition, some sample data and what you want the select statement to produce as output?"
ron
-
I hate to guess... that's why I didn't bother to even try on this one.
-
Ok, I hope this example works:
Table: Items
ItemID UniqueID Description
1 1 1 ct saphire ring
2 2 1 ct ruby ring
3 2 1 ct ruby ring
4 3 1 ct diamond ring
5 4 .5 ct amythist pendant
5 4 .5 ct amythist pendant
6 4 .5 ct amythist pendant
Table: Item Descrption
ItemID Status StatusChangeDate
1 1 1/1/1900
2 2 3/5/05
3 1 1/1/1900
4 1 1/1/1900
5 2 5/10/05
6 1 1/1/1900
7 3 6/10/05
Table: ItemStatus
StatusID StatusDescription
1 For sale
2 Sold
3 Returned
Output:
UniqueID Description LatestStatus
1 1 ct Saphire Ring For sale
2 1 ct Ruby Ring Sold
3 1 ct Diamond Ring For Sale
4 .5 ct Amythist Ring Returned
-
I'm gonna assume that the 2nd
"5 4 .5 ct amythist pendant"
doesn't belong there (unless you have duplicate data).
I'm not sure I understand the data model but here's what I came up with :
Select IT.UniqueID, IT.Description, IIS.StatusDescription
from
dbo.ItemsDescriptions Main inner join
(
Select ItemID, MAX(StatusChangeDate) as StatusChangeDate
from dbo.ItemsDescriptions
group by ItemID
) dtLatestStatus
on Main.ItemID = dtLatestStatus.ItemID and Main.StatusChangeDate = dtLatestStatus.StatusChangeDate
inner join dbo.ItemStatus ISS on Main.Status = IIS.StatusID
inner join dbo.Items IT on dtLatestStatus.ItemID = IT.ItemID
-
I will try the query out. Thanks. I know, the duplicate data is a nightmare....
-
You'll have to fix the data, or the query will fail.
The distinct clause might help you here but it's gonna kill what little performance there can be in there.
-
This one was really bugging me, so I put a bit of effort into it. It may be all worthless if Remi's answer worked.
I tried with the duplicate data, (I also had to include an Items.ItemID = 7 value as the output looked like it was supposed to be there), and found it to an odd report, but a good challenge. I'm not really sure what this is showing, but that is for the person who requested it.
The post had great data to use for testings, so here goes, (let me know if this works or was just a waste of time):
CREATE TABLE #Items( ItemID integer,
UniqueID integer,
[Description] varchar(25))
INSERT INTO #Items( ItemID, UniqueID, [Description])
VALUES( 1, 1, '1.0 ct Sapphire Ring')
INSERT INTO #Items( ItemID, UniqueID, [Description])
VALUES( 2, 2, '1.0 ct Ruby Ring')
INSERT INTO #Items( ItemID, UniqueID, [Description])
VALUES( 3, 2, '1.0 ct Ruby Ring')
INSERT INTO #Items( ItemID, UniqueID, [Description])
VALUES( 4, 3, '1.0 ct Diamond Ring')
INSERT INTO #Items( ItemID, UniqueID, [Description])
VALUES( 5, 4, '0.5 ct Amethyst Pendant')
INSERT INTO #Items( ItemID, UniqueID, [Description])
VALUES( 5, 4, '0.5 ct Amethyst Pendant')
INSERT INTO #Items( ItemID, UniqueID, [Description])
VALUES( 6, 4, '0.5 ct Amethyst Pendant')
INSERT INTO #Items( ItemID, UniqueID, [Description])
VALUES( 7, 4, '0.5 ct Amethyst Pendant')
------------------------------------------------------------------------------------------
CREATE TABLE #ItemDescription( ItemID integer,
Status integer,
StatusChangeDate datetime)
INSERT INTO #ItemDescription( ItemID, Status, StatusChangeDate)
VALUES( 1, 1, '01/01/1900')
INSERT INTO #ItemDescription( ItemID, Status, StatusChangeDate)
VALUES( 2, 2, '03/05/2005')
INSERT INTO #ItemDescription( ItemID, Status, StatusChangeDate)
VALUES( 3, 1, '01/01/1900')
INSERT INTO #ItemDescription( ItemID, Status, StatusChangeDate)
VALUES( 4, 1, '01/01/1900')
INSERT INTO #ItemDescription( ItemID, Status, StatusChangeDate)
VALUES( 5, 2, '05/10/2005')
INSERT INTO #ItemDescription( ItemID, Status, StatusChangeDate)
VALUES( 6, 1, '01/01/1900')
INSERT INTO #ItemDescription( ItemID, Status, StatusChangeDate)
VALUES( 7, 3, '06/10/2005')
------------------------------------------------------------------------------------------
CREATE TABLE #ItemStatus( StatusID integer,
StatusDescription varchar(10))
INSERT INTO #ItemStatus( StatusID,StatusDescription)
VALUES( 1, 'For sale')
INSERT INTO #ItemStatus( StatusID,StatusDescription)
VALUES( 2, 'Sold')
INSERT INTO #ItemStatus( StatusID,StatusDescription)
VALUES( 3, 'Returned')
------------------------------------------------------------------------------------------
SELECT #Item.UniqueID,
#Item.[Description],
#ItemStatus.StatusDescription AS LatestStatus
FROM #ItemDescription
INNER JOIN( SELECT MAX( #Items.UniqueID) AS UniqueID,
MAX( #Items.ItemID) AS ItemID, #Items.[Description]
FROM #Items GROUP BY #Items.[Description]) #Item
ON( #Item.ItemID = #ItemDescription.ItemID)
INNER JOIN( SELECT MAX( #ItemDescription.Status) AS Status, #Items.UniqueID
FROM #ItemDescription
INNER JOIN #Items ON( #ItemDescription.ItemID = #Items.ItemID)
GROUP BY #Items.UniqueID) #ItemDescriptionStatus
ON( #Item.UniqueID = #ItemDescriptionStatus.UniqueID)
INNER JOIN #ItemStatus ON( #ItemDescriptionStatus.Status = #ItemStatus.StatusID)
ORDER BY #Item.UniqueID
------------------------------------------------------------------------------------------
/* Output:
UniqueID Description LatestStatus
1 1 ct Saphire Ring For sale
2 1 ct Ruby Ring Sold
3 1 ct Diamond Ring For Sale
4 .5 ct Amythist Ring Returned */
DROP TABLE #Items
DROP TABLE #ItemDescription
DROP TABLE #ItemStatus
I wasn't born stupid - I had to study.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply