It's the classic EAV pattern, which is a strong indicator of a design issue. I hope you understand that this design is against the core rules of relational databases. I have a blog post on this subject if you want to dig deeper.
However, if the design can't be changed, you can use a PIVOT
query to extract your data:
DECLARE @BaseTable TABLE (
IDKey int PRIMARY KEY,
Name varchar(50)
)
INSERT INTO @BaseTable
VALUES
(1, 'Ian'),
(2, 'John'),
(3, 'Ann');
DECLARE @Table1 TABLE (
ID int,
Amount int,
Type char(1),
PRIMARY KEY (ID, Type)
)
INSERT INTO @Table1
VALUES
(1, 100, 'X'),
(1, 200, 'Y'),
(2, 300, 'X'),
(2, 400, 'Y'),
(3, 500, 'X'),
(3, 600, 'Y');
SELECT *
FROM @BaseTable AS B
INNER JOIN (
SELECT ID, X AS AmountX, Y AS AmountY
FROM @Table1 AS T
PIVOT (MIN(Amount) FOR Type IN ([X],[Y])) AS P
) AS T1
ON B.IDKey = T1.ID;
Logins (on the server) do not automatically correspond to users (in the database). For instance, "sa" does not have its own user in the database, but uses "dbo". This might get you started.
SELECT s.session_id,
sp.[type], sp.type_desc,
sp.[name] AS loginName, dp.[name] AS userName,
sp.is_disabled
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.database_principals AS dp ON s.security_id=dp.[sid]
LEFT JOIN sys.server_principals AS sp ON s.security_id=sp.[sid];
Note: sysprocesses
, sysusers
, etc are deprecated and will probably stop working in a future version of SQL Server.
I go into a bit more detail on SQL Server security in my blog post.
Best Answer
This is called an "anti-join" (or anti-semijoin). One way to write it in SQL is to use the
NOT EXISTS
construction:If - as your comment - you want a parent row (at Table1) not related to any child row (at Table2), to NOT be shown, add this (or join Table2 if you also want columns from the second table shown):