Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to get Primary Key Value if not Present in Foreign Key via Join? Expand / Collapse
Author
Message
Posted Sunday, May 4, 2014 3:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 19, 2014 12:29 AM
Points: 19, Visits: 41
I've following query which display the result as required, but I need to get the missing Primary Key Values which are not available in the result:
SELECT	A.SignedByUserID, B.FullName, COUNT(A.OutletID) AS TotalSignups, DATENAME(Month, A.SignupDate) AS Month
FROM dbo.tblMer_Outlet AS A LEFT OUTER JOIN dbo.tblGen_Users AS B ON A.SignedByUserID = B.UserID
WHERE (A.SignupDate >= '2014-04-01 00:00:00' AND A.SignupDate <= '2014-04-30 23:59:59')
GROUP BY A.SignedByUserID, B.FullName, DATENAME(Month, A.SignupDate)

This Query returns the following result:
SignedByUserID     FullName       TotalSignups     Month
--------------------------------------------------------
9 Babu Raj 16 April
11 Faheem 19 April
39 Fasil Abbas 16 April
29 Hafiz Suleman 10 April
12 Hussain Abbas 16 April
15 Khawaja Aashan 33 April
33 M. Danyal 16 April
41 M. Qasim 01 April
32 M. Yousuf 16 April
37 Noman Yousaf 14 April
40 Sajid Saleem 16 April
5 Sales 10 April
20 Tauseef Anees 23 April
35 Umar Akbar 11 April
22 Willie 09 April

but my User table contains more value:
SELECT	UserID, FullName
FROM tblGen_Users WHERE GroupID = 4 AND UserID NOT IN (
SELECT SignedByUserID
FROM tblMer_Outlet
WHERE (SignupDate >= '2014-04-01 00:00:00' AND SignupDate <= '2014-04-30 23:59:59')
)

SignedByUserID     FullName
---------------------------
38 Bilal Mateen

which does not have a signup for the month of April, but I need it to be available for this or any upcoming month. I need this orr all users, which does not exists in the context needs to be displayed in the result.
Post #1567322
Posted Sunday, May 4, 2014 4:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 2,217, Visits: 5,998
Quite few ways of doing this, in essence create a set of all customers/months and then do the count.
The server normally translates queries such as "select * from A left outer join B where B.xx = yy" to an inner join.


/* For lack of other data */
USE AdventureWorks2012;
GO
;WITH ORDER_MONTH AS
(
/* All Order Months */
SELECT DISTINCT
(YEAR(SOH.OrderDate) * 100) + MONTH(SOH.OrderDate) AS ISO_MONTH
FROM Sales.SalesOrderHeader SOH
)
,CUST_MONTH AS
(
/* All Customer Month combinations */
SELECT
SACU.CustomerID
,OM.ISO_MONTH
FROM Sales.Customer SACU
CROSS APPLY ORDER_MONTH OM
)
,CUST_MONTH_COUNT AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY
CM.CustomerID
,CM.ISO_MONTH
ORDER BY
(SELECT NULL)
) AS CUMO_RID
,CM.CustomerID
,CM.ISO_MONTH
,ISNULL(COUNT(SOH.SalesOrderID) OVER
(
PARTITION BY
CM.CustomerID
,YEAR(SOH.OrderDate), MONTH(SOH.OrderDate)
),0) AS ORDER_COUNT

FROM CUST_MONTH CM
LEFT OUTER JOIN Sales.SalesOrderHeader SOH
ON CM.CustomerID = SOH.CustomerID
AND CM.ISO_MONTH = (YEAR(SOH.OrderDate) * 100) + MONTH(SOH.OrderDate)
)

SELECT
CMC.CustomerID
,CMC.ISO_MONTH
,CMC.ORDER_COUNT
FROM CUST_MONTH_COUNT CMC
WHERE CMC.CUMO_RID = 1
AND CMC.ISO_MONTH = 200803;

Post #1567327
Posted Sunday, May 4, 2014 8:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:02 PM
Points: 20,734, Visits: 32,515
eligiable (5/4/2014)
I've following query which display the result as required, but I need to get the missing Primary Key Values which are not available in the result:
SELECT	A.SignedByUserID, B.FullName, COUNT(A.OutletID) AS TotalSignups, DATENAME(Month, A.SignupDate) AS Month
FROM dbo.tblMer_Outlet AS A LEFT OUTER JOIN dbo.tblGen_Users AS B ON A.SignedByUserID = B.UserID
WHERE (A.SignupDate >= '2014-04-01 00:00:00' AND A.SignupDate <= '2014-04-30 23:59:59')
GROUP BY A.SignedByUserID, B.FullName, DATENAME(Month, A.SignupDate)

This Query returns the following result:
SignedByUserID     FullName       TotalSignups     Month
--------------------------------------------------------
9 Babu Raj 16 April
11 Faheem 19 April
39 Fasil Abbas 16 April
29 Hafiz Suleman 10 April
12 Hussain Abbas 16 April
15 Khawaja Aashan 33 April
33 M. Danyal 16 April
41 M. Qasim 01 April
32 M. Yousuf 16 April
37 Noman Yousaf 14 April
40 Sajid Saleem 16 April
5 Sales 10 April
20 Tauseef Anees 23 April
35 Umar Akbar 11 April
22 Willie 09 April

but my User table contains more value:
SELECT	UserID, FullName
FROM tblGen_Users WHERE GroupID = 4 AND UserID NOT IN (
SELECT SignedByUserID
FROM tblMer_Outlet
WHERE (SignupDate >= '2014-04-01 00:00:00' AND SignupDate <= '2014-04-30 23:59:59')
)

SignedByUserID     FullName
---------------------------
38 Bilal Mateen

which does not have a signup for the month of April, but I need it to be available for this or any upcoming month. I need this orr all users, which does not exists in the context needs to be displayed in the result.


Careful, the two queries above do not say same thing. Please read the comments I put in the code below.


SELECT
A.SignedByUserID,
B.FullName,
COUNT(A.OutletID) AS TotalSignups,
DATENAME(Month, A.SignupDate) AS Month
FROM
/*
select all rows from dbo.tblMer_Outlet whether there is
a matching row in dbo.tblGen_Users or not.
*/
dbo.tblMer_Outlet AS A
LEFT OUTER JOIN dbo.tblGen_Users AS B
ON A.SignedByUserID = B.UserID
WHERE
-- A.SignupDate >= '2014-04-01 00:00:00' AND
-- A.SignupDate <= '2014-04-30 23:59:59')
A.SignupDate >= '2014-04-01 00:00:00' AND
A.SignupDate < '2014-05-01 00:00:00') -- Insures that any time > 2014-04-30 23:59:59
-- and < 2014-05-01 00:00:00 are caught
GROUP BY
A.SignedByUserID,
B.FullName,
DATENAME(Month, A.SignupDate);

/*********************/

SELECT
UserID,
FullName
FROM
tblGen_Users
WHERE
GroupID = 4 AND
/*
Select all rows where the UserID is not in dbo.tblMer_Outlet
*/
UserID NOT IN (
SELECT
SignedByUserID
FROM
tblMer_Outlet
WHERE
SignupDate >= '2014-04-01 00:00:00' AND
SignupDate < '2014-05-01 00:00:00' -- same change as above
);





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1567338
Posted Sunday, May 4, 2014 11:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 19, 2014 12:29 AM
Points: 19, Visits: 41
Lynn Pettis! The 2nd Query is just for reference, that what I'm trying to get into the results. My main Table is tblGen_Users and UserID is PK, I've tried every logic, I can think; but no luck.

SELECT
A.SignedByUserID,
B.FullName,
COUNT(A.OutletID) AS TotalSignups,
DATENAME(Month, A.SignupDate) AS Month
FROM
dbo.tblGen_Users AS B LEFT OUTER JOIN
--The tblGen_Users contains all the values
dbo.tblMer_Outlet AS A ON B.UserID = A.SignedByUserID
--The tblMer_Outlet contains only the selected values

/* If the values from tblGen_Users are not available in tblGen_Merchant, the missing values must be displayed in the result mentioning 0 under COUNT */

WHERE
(A.SignupDate >= '2014-04-01 00:00:00') AND
(A.SignupDate <= '2014-04-30 23:59:59')
GROUP BY
A.SignedByUserID,
B.FullName,
DATENAME(Month, A.SignupDate)
ORDER BY
B.FullName

Post #1567345
Posted Sunday, May 4, 2014 12:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 19, 2014 12:29 AM
Points: 19, Visits: 41
I've make this Fiddle and is running fine, I don't get it where is the problem:
http://sqlfiddle.com/#!6/65744/6/0
Post #1567351
Posted Sunday, May 4, 2014 12:39 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 2,217, Visits: 5,998
eligiable (5/4/2014)
I've make this Fiddle and is running fine, I don't get it where is the problem:
http://sqlfiddle.com/#!6/65744/6/0


What ever happened to Baby Jane, oops, sorry, Bilal Mateen's ID?
Post #1567353
Posted Sunday, May 4, 2014 1:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 19, 2014 12:29 AM
Points: 19, Visits: 41
It's just the SignedByUserID, that for Bilal Mateen does not exists in tblMer_Outlet, so we can change it to B.UserID from tblGen_Users, both are the same, but that not concern to me.

The main thing I'm not getting the desired result in My Query.
Post #1567357
Posted Sunday, May 4, 2014 1:11 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 2,217, Visits: 5,998
eligiable (5/4/2014)
It's just the SignedByUserID, that for Bilal Mateen does not exists in tblMer_Outlet, so we can change it to B.UserID from tblGen_Users, both are the same, but that not concern to me.

The main thing I'm not getting the desired result in My Query.


Good stuff, just wandered if you'd noticed. And then of course, you could also coalesce the count to a 0 and the month name.
Post #1567359
Posted Sunday, May 4, 2014 1:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 19, 2014 12:29 AM
Points: 19, Visits: 41
COALESCE will work if the query returns a NULL value, but the thing is; in my actual case it's not returning.
Post #1567364
Posted Sunday, May 4, 2014 1:41 PM This worked for the OP Answer marked as solution
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 19, 2014 12:29 AM
Points: 19, Visits: 41
Finally got something Working ...
SELECT A.UserID, A.FullName, ISNULL(B.TotalSignups,0) AS TotalSignups, B.Month FROM
(
SELECT UserID, FullName
FROM tblGen_Users
WHERE GroupID = 4
) AS A LEFT OUTER JOIN
(
SELECT SignedByUserID, COUNT(1) AS TotalSignups, DATENAME(Month, SignupDate) AS Month
FROM tblMer_Outlet
WHERE SignupDate BETWEEN '2014-04-01' AND '2014-05-01'
GROUP BY SignedByUserID, DATENAME(Month, SignupDate)
) AS B ON A.UserID = B.SignedByUserID
ORDER BY A.FullName

Post #1567365
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse