Last post May 16, 2017 11:08 AM by PatriceSc
May 16, 2017 09:52 AM|sanjaykumar pushadapu|LINK
i want inner join tow table but
id page user admin
1 login 1 1
2 Home1 0 1
here how to iner join tow table and how to get login page value based on role
May 16, 2017 11:03 AM|kaushalparik27|LINK
With this table structure, you can use dynamic query to get the result, here is an example:
-- table schema for demonstration
CREATE TABLE #Table1 (id BIGINT, Role VARCHAR(10))
CREATE TABLE #Table2 (id BIGINT, Page VARCHAR(10), Users BIT, Admin BIT)
-- inserting sample data for demonstration
INSERT INTO #Table1 SELECT 1,'User' UNION ALL SELECT 2,'Admin'
INSERT INTO #Table2 SELECT 1,'Login',1,1 UNION ALL SELECT 2,'Home',0,1
-- passing FieldName as parameter
DECLARE @FieldName VARCHAR(10) = 'Admin'
-- actual dynamic query
EXEC('SELECT id, Page FROM #Table2 WHERE ISNULL(' + @FieldName + ',0) = 1')
-- dropping schema tables
DROP TABLE #Table1, #Table2
However, I suggest to alter schema/columns for Table2 to be: Id, Page, RoleId. Here RoleId will be the ID from Table1. So, this way you will not required to add as many columns as Roles (Users, Admin etc) in Table2
May 16, 2017 11:08 AM|PatriceSc|LINK
Not crystal clear but you'll likely have to use dynamic SQL (for example sp_executesql). You can use SQL parameters where literal values are expected but not for example where a column or table name is expected.
Aslo the design for table 2 seems wrong to me. Basically you are using the column name as a data. It should be a row as done for table-1.