Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to get Primary Key Value if not Present in Foreign Key via Join?


How to get Primary Key Value if not Present in Foreign Key via Join?

Author
Message
eligiable
eligiable
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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.
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6695 Visits: 17690
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.
Cool

/* 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;


Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
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
);




Cool
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)
eligiable
eligiable
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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


eligiable
eligiable
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6695 Visits: 17690
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?
Cool
eligiable
eligiable
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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.
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6695 Visits: 17690
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.
Cool
eligiable
eligiable
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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.
eligiable
eligiable
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search