Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query help

Author

Topic

wvjonathan
Starting Member

5 Posts

Posted - 2006-05-18 : 14:44:04

I posted this in the SQL intro, and developers forums, but received no comments. Maybe someone here can help?

Is it possible to combine these two queries into a single query? I've left joined the history to the first query, but if there are multiple entries in the history table, I get duplications in the resulting record set. My hope is to only show the most recent history event for that record.

The first query joins a Cell Phone billing table with both Inventory and User Tables. The second looks for the most recent status history record for a given cell number and joins it to the Inventory and User tables.

This is probably incredibly simple, but I've been looking at this for a while and I think I'm too frustrated to see the solution.

SELECT *
FROM tblCellBill LEFT OUTER JOIN
tblInventory ON tblCellBill.CellNumber = tblInventory.CellNumber LEFT OUTER JOIN
tblUsers ON tblInventory.AssignedID = tblUsers.UserID
ORDER BY tblCellBill.BilledAmount DESC, tblUsers.Lastname, tblUsers.FirstName

SELECT TOP 1 *
FROM tblHistory LEFT OUTER JOIN
tblInventory ON tblHistory.HistoryID = tblInventory.InventoryID LEFT OUTER JOIN
tblUsers ON tblUsers.UserID = tblHistory.AssignedID LEFT OUTER JOIN
tblInventoryStatus ON tblHistory.StatusID = tblInventoryStatus.StatusID
WHERE (tblInventory.CellNumber = [The cell number from the query above])
ORDER BY tblHistory.EntryDate DESC

tblCellBill
--------
CellNumber BilledAmount

tblInventory
--------
InventoryID CellNumber AssignedID

tblUsers
--------
UserID LastName FirstName

tblHistory
--------
HistoryID AssignedID StatusID

tblInventoryStatus
--------
StatusID Status

wvjonathan
Starting Member

5 Posts

Posted - 2006-05-18 : 15:37:26

Well it was simple after all; I guess I was just tired.

SELECT DISTINCT ....

and LEFT JOIN tblhistory

I'd like to give everyone a thanks for all the help, but ... ;-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-18 : 15:41:49

Next time to receive an answer, you'll need to post some sample data for the tables involved in your queries, plus the expected result set using that sample data. Given this information, we'll be able to decipher what you want. Without the information, your question may go unanswered (like in this case). We just didn't have enough information to assist you.

Tara Kizer
aka tduggan

wvjonathan
Starting Member

5 Posts

Posted - 2006-05-18 : 16:37:35

Hi Tara,

I appreciate your comment. I did look around on the site before posting to see if there was a preferred format for posting (and to try to find a solution) but judging from everyone else's requests there didn't seem to be one.

Oh well, live and learn. Thanks for the info.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources