6 Replies - 1193 Views - Last Post: 05 December 2012 - 10:26 PM Rate Topic: -----

#1 Lethugs  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 22-November 12

Query 4 tables count record with advanced search on SQL Server 2008

Posted 05 December 2012 - 03:35 AM

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


Is This A Good Question/Topic? 0
  • +

Replies To: Query 4 tables count record with advanced search on SQL Server 2008

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,462
  • Joined: 12-June 08

Re: Query 4 tables count record with advanced search on SQL Server 2008

Posted 05 December 2012 - 09:59 AM

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.
Was This Post Helpful? 0
  • +
  • -

#3 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 433
  • View blog
  • Posts: 2,998
  • Joined: 12-January 10

Re: Query 4 tables count record with advanced search on SQL Server 2008

Posted 05 December 2012 - 10:11 AM

wow modi you didnt yell at him for not using code tags-----youre slipping
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,462
  • Joined: 12-June 08

Re: Query 4 tables count record with advanced search on SQL Server 2008

Posted 05 December 2012 - 10:18 AM

I basically ignored that big ol' pile. It's been one of them days.
Was This Post Helpful? 0
  • +
  • -

#5 Lethugs  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 22-November 12

Re: Query 4 tables count record with advanced search on SQL Server 2008

Posted 05 December 2012 - 07:26 PM

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
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,462
  • Joined: 12-June 08

Re: Query 4 tables count record with advanced search on SQL Server 2008

Posted 05 December 2012 - 07:51 PM

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.
Was This Post Helpful? 0
  • +
  • -

#7 Lethugs  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 22-November 12

Re: Query 4 tables count record with advanced search on SQL Server 2008

Posted 05 December 2012 - 10:26 PM

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

anyway i figured it out, thanks to someone out there who gave me idea.
thank you for considering my post :D
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1