[Lethugs]

I have these tables

User
Description
Item
Transaction Details

User

UID Name
1 Jose
2 James
3 Mike

Description

DeID Dgroup
1 Printer
2 Monitor
3 Keyboard

Item

ID DeID Name
1 1 T10
2 1 Eps
3 2 View
4 3 Tech

Transaction Details

TranNo Ttype IID OUID UserID Date
1 Releasing 1 1 11/23/2010
2 Releasing 2 1 01/18/2011
3 Transfer 1 1 2 03/26/2011
4 Releasing 3 2 03/30/2011
5 ItemTransfer 3 2 3 11/21/2012
6 Repair 1 2 12/26/2012


How to query database such that when I select printer in description, It will show the Name in User table and count how many printer he have. IT will not include if the Item is transferred to other user.

eg. Select printer

Name count
Jose 1
James 1

if Select Monitor

Name Count
Mike 1

Im trying this code shared to me but not generating the desired output (thanks to BitBlt)

"SELECT [w].[FN] AS UserName, [w].[UID] AS UserID, COUNT(ISNULL(z.DeId, z1.DeId)) AS Count FROM [User] w LEFT OUTER JOIN [Transaction Details] x ON [x].[OUID] = [w].[UID] LEFT OUTER JOIN [Item] y ON [y].[IID] = [x].[IID] LEFT OUTER JOIN [Description] z ON z.DeID = y.DeID AND [z].[DeID] = '" & txtDescID.Text & "' " & _
                                              "LEFT OUTER JOIN [Transaction Details] x1 ON x1.UserID = [w].[UID] LEFT OUTER JOIN [Item] y1 ON [y1].[IID] = [x1].[IID] LEFT OUTER JOIN [Description] z1 ON z1.DeID = y1.DeID AND z1.DeID = '" & txtDescID.Text & "' " & _
                                              "WHERE ([x].[UserID] IS NULL AND NOT EXISTS (SELECT 1 FROM [Transaction Details] x2 WHERE x2.[OUID] = w.[UID] AND x2.IID = x.IID AND x2.[UserID] IS NOT NULL)) " & _
                                              "OR (x1.[UserID] IS NOT NULL AND NOT EXISTS (SELECT 1 FROM [Transaction Details] x3 WHERE x3.[UserID] = w.[UID] AND x3.[IID] = x1.[IID] AND x3.[UserID] IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM [Transaction Details] x4 WHERE x4.[OUID] = w.[UID] AND x4.[IID] = x1.[IID] AND x4.[UserID] IS NOT NULL)) GROUP BY w.[UID], [w].[FN]

This post has been edited by modi123_1: 05 December 2012 - 10:18 AM
Reason for edit:: highlight the text and just click the 'code' button in the text editor

[modi123_1]

It should be a pretty straight forward query.


So the big hurdle is the 'Transaction Details' table.

This is a simple one with a group by.

select userid, count(*)
from 'Transaction Details'
where ttype <> 'ItemTransfer'
-- the spot to put in the limit by the description
group by userid


I don't see where the 'Description' table is being used in this table.. but basically you would limit the query by the type in the where statement.

You take that query and join that against the 'User' table to get the proper name.

[DarenR]

wow modi you didnt yell at him for not using code tags-----youre slipping

[modi123_1]

I basically ignored that big ol' pile. It's been one of them days.

[Lethugs]

The problem there is that it should only count ttype = Releasing then subtract if it is transferred.

e.g If a printer is released to a user (Jose) then counted as his accountability, but if it is transferred to another user (James), it should be deducted from his accountability, but there will be a case that that printer will return to Jose.

How will I join the User table to transaction table having two possible user. And what if no released item to Mike but has a transferred item? He also has accountability

[modi123_1]

See... these are the crazy requirements that should have been mentioned.. but you didn't.. and they are silly complex for me to tease out. Good luck.

[Lethugs]

Sorry for not including those things, I guess i didn't explained my problem and its possible situations and scenarios.

anyway i figured it out, thanks to someone out there who gave me idea.
thank you for considering my post