January 20, 2011 at 2:54 am
PROBLEM:
<table a>
userid
username
isadmin
<table b>
companyid
invoiceno
invoiceamt
userid
I need to pickup the invoiceno and invoicemat from table b
based on the login (userid), but at the same time if the user is administrator (isadmin)
then I need to return all the invoices (irrespective of userid)
that is, it should not check for the user
NEED SOLUTION AS A SELECT STATEMENT NOT AS PROCEDURE
January 20, 2011 at 3:00 am
Please can we see what you've tried so far? Also, DDL in the form of CREATE TABLE statements and sample data in the form of INSERT statements would also be helpful and will make it more likely that someone will make the effort to assist you.
Thanks
John
January 20, 2011 at 4:01 am
Dear Friend,
PFB the DDL Script................ I made a lot of attempt by using "case" and "temp" table etc... but could not arrive any solution.. looking for a good solution..
/****** Object: Table [dbo].[Table_A] Script Date: 01/20/2011 16:25:33 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_A]') AND type in (N'U'))
DROP TABLE [dbo].[Table_A]
GO
/****** Object: Table [dbo].[Table_B] Script Date: 01/20/2011 16:25:33 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_B]') AND type in (N'U'))
DROP TABLE [dbo].[Table_B]
GO
/****** Object: Table [dbo].[Table_B] Script Date: 01/20/2011 16:25:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_B]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Table_B](
[ID] [int] IDENTITY(1,1) NOT NULL,
[COMPANYID] [int] NULL,
[INVOICENO] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[INVOICEAMT] [float] NULL,
[USERID] [int] NULL
)
END
GO
SET IDENTITY_INSERT [dbo].[Table_B] ON
INSERT [dbo].[Table_B] ([ID], [COMPANYID], [INVOICENO], [INVOICEAMT], [USERID]) VALUES (1, 101, N'2010-11/JAN/0112', 100, 1)
INSERT [dbo].[Table_B] ([ID], [COMPANYID], [INVOICENO], [INVOICEAMT], [USERID]) VALUES (2, 101, N'2010-11/JAN/0113', 150, 2)
INSERT [dbo].[Table_B] ([ID], [COMPANYID], [INVOICENO], [INVOICEAMT], [USERID]) VALUES (3, 101, N'2010-11/JAN/0114', 125, 1)
INSERT [dbo].[Table_B] ([ID], [COMPANYID], [INVOICENO], [INVOICEAMT], [USERID]) VALUES (4, 101, N'2010-11/JAN/0115', 220, 3)
INSERT [dbo].[Table_B] ([ID], [COMPANYID], [INVOICENO], [INVOICEAMT], [USERID]) VALUES (5, 120, N'2010-11/JAN/0116', 555, 3)
INSERT [dbo].[Table_B] ([ID], [COMPANYID], [INVOICENO], [INVOICEAMT], [USERID]) VALUES (6, 120, N'2010-11/JAN/0117', 450, 1)
INSERT [dbo].[Table_B] ([ID], [COMPANYID], [INVOICENO], [INVOICEAMT], [USERID]) VALUES (7, 120, N'2010-11/JAN/0118', 375, 1)
INSERT [dbo].[Table_B] ([ID], [COMPANYID], [INVOICENO], [INVOICEAMT], [USERID]) VALUES (8, 120, N'2010-11/JAN/0119', 400, 3)
INSERT [dbo].[Table_B] ([ID], [COMPANYID], [INVOICENO], [INVOICEAMT], [USERID]) VALUES (9, 120, N'2010-11/JAN/0120', 350, 2)
INSERT [dbo].[Table_B] ([ID], [COMPANYID], [INVOICENO], [INVOICEAMT], [USERID]) VALUES (10, 121, N'2010-11/JAN/0121', 500, 3)
INSERT [dbo].[Table_B] ([ID], [COMPANYID], [INVOICENO], [INVOICEAMT], [USERID]) VALUES (11, 121, N'2010-11/JAN/0122', 350, 1)
INSERT [dbo].[Table_B] ([ID], [COMPANYID], [INVOICENO], [INVOICEAMT], [USERID]) VALUES (12, 121, N'2010-11/JAN/0123', 250, 2)
SET IDENTITY_INSERT [dbo].[Table_B] OFF
/****** Object: Table [dbo].[Table_A] Script Date: 01/20/2011 16:25:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_A]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Table_A](
[ID] [int] IDENTITY(1,1) NOT NULL,
[USERID] [int] NULL,
[USERNAME] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISADMIN] [int] NULL
)
END
GO
SET IDENTITY_INSERT [dbo].[Table_A] ON
INSERT [dbo].[Table_A] ([ID], [USERID], [USERNAME], [ISADMIN]) VALUES (1, 1, N'MIKE', 0)
INSERT [dbo].[Table_A] ([ID], [USERID], [USERNAME], [ISADMIN]) VALUES (2, 2, N'NILOFAR', 0)
INSERT [dbo].[Table_A] ([ID], [USERID], [USERNAME], [ISADMIN]) VALUES (3, 3, N'SABY', 0)
INSERT [dbo].[Table_A] ([ID], [USERID], [USERNAME], [ISADMIN]) VALUES (4, 4, N'MSR', 1)
SET IDENTITY_INSERT [dbo].[Table_A] OFF
January 20, 2011 at 5:04 am
Something like this?
select
A.[USERID]
,A.[USERNAME]
,B.[INVOICENO]
,B.[INVOICEAMT]
from
Table_A a
left join Table_B b on a.USERID = CASE WHEN A.ISADMIN = 1 THEN A.USERID ELSE B.USERID END
January 20, 2011 at 5:17 am
Dear Friend,
Thanks for your effort and quick reply.
The actual problem is:
If the logged user is NOT ADMIN then I should fetch the invoices pertaining to that user.
If the logged user is an ADMIN then I should fetch all the invoices.
The clutch is 'condition based on a result of condition'.
Thanks
January 20, 2011 at 5:24 am
I cannot make sense of what you are trying to say.
Please show the expected result for the data you have created.
January 20, 2011 at 6:06 am
Table_A
ID USERID USERNAME ISADMIN
11 MIKE 0
22 NILOFAR 0
33 SABY 0
44 MSR 1 --> ADMIN GUY
Table_B
ID COMPANYID INVOICENO INVAMT USERID
1 101 2010-11/JAN/0112100 1
2 101 2010-11/JAN/0113150 2
3 101 2010-11/JAN/0114125 1
4 101 2010-11/JAN/0115220 3
5 120 2010-11/JAN/0116555 3
6 120 2010-11/JAN/0117450 1
7 120 2010-11/JAN/0118375 1
8 120 2010-11/JAN/0119400 3
9 120 2010-11/JAN/0120350 2
10 121 2010-11/JAN/0121500 3
11 121 2010-11/JAN/0122350 1
12 121 2010-11/JAN/0123250 2
Ok, Now the user "SABY" logs in, then he should be able to view only his invoices(total 4), which is in bold.
On the other hand, if user "MSR" logs in, the he should be able to view all the invoices (total 12), cos he is "Admin" and his userid should not be considered.
Hope you are clear now..
this is the exact problem.. I dont know how to explain more detail than this..
January 20, 2011 at 6:17 am
So just add a WHERE clause on the end of tertiusdp's code so that only the results for the user you want are returned.
John
January 20, 2011 at 6:21 am
If you want to filter for the current logged in user, try a where clause
WHERE A.[USERNAME] = SUSER_NAME()
You might want to look into the following functions to get the current logged in user name:
SUSER_NAME(), USER_NAME()
January 20, 2011 at 6:57 am
WITH A (LOW,HIGH)
AS (SELECT CASE WHEN ISADMIN=1 THEN 1 ELSE USERID END AS [LOW],
CASE WHEN ISADMIN=1 THEN 2147483647 ELSE USERID END AS [HIGH]
FROM dbo.Table_A
WHERE USERNAME = 'SABY')
SELECT B.INVOICENO,B.USERID
FROM A
JOIN dbo.Table_B B ON B.USERID BETWEEN A.LOW AND A.HIGH
Far away is close at hand in the images of elsewhere.
Anon.
January 20, 2011 at 7:27 am
How about this ?
DECLARE @USERNAME VARCHAR(100)
SET @USERNAME = 'MIKE'
SELECT B.*
FROM Table_B B
JOIN Table_A A
ON A.USERID = B.USERID
AND A.USERNAME = @USERNAME
UNION ALL
SELECT *
FROM Table_B B
WHERE 1 = (SELECT CASE WHEN ISADMIN = 1 THEN 1 ELSE 0 END FROM TABLE_A WHERE USERNAME = @USERNAME )
AND USERID IN (SELECT USERID FROM Table_A WHERE USERNAME <> @USERNAME)
January 20, 2011 at 9:25 pm
Or a tweaked version of my above code:
DECLARE @USERNAME NVARCHAR(100) ; SET @USERNAME = N'SABY' ;
DECLARE @userid INT , @ISADMIN INT
SELECT @userid = USERID , @ISADMIN = ISADMIN
FROM Table_A
WHERE USERNAME = @USERNAME
SELECT B.*
FROM Table_B B
WHERE B.USERID = @userid
UNION ALL
SELECT *
FROM Table_B B
WHERE 1 = @ISADMIN
AND USERID IN (SELECT USERID FROM Table_A WHERE USERID <> @userid)
January 20, 2011 at 9:37 pm
But in any case, David's code will beat mine pants down..
January 20, 2011 at 9:50 pm
Dear John Mitchell, tertiusdp, David Burrows, ColdCoffee :
Thanks for your excellent effort. With all your help I got it what I want.
Great effort.. Once again thanking you all for made my job simpler.
Raghunathan
"Every problem has a hidden opportunity"
January 21, 2011 at 4:07 am
You are most welcome. I love it when a plan comes together!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply