January 20, 2012 at 3:11 pm
I am able to write the query I need from each table, but I am not sure how to go about combining them. I need all of the UserIds to display from the User table, and I need one column that displays a total of all the Asset Events and separate column that displays a total of all the Inventory Events. I basically need all of the results from each of the tables, but my joins are only giving me results from the Events table and leaving out User Ids with no events against them.
*****************************************
SELECT
AssetEvents.UserIdCreated,
COUNT (AssetEvents.TimeCreated) AS [Total Asset]
FROM
AssetEvents
WHERE
AssetEvents.TimeCreated between '1/1/2012' and '1/7/2012'
GROUP BY
AssetEvents.UserIdCreated
*************************************************
SELECT
InventoryEvents.UserIdCreated,
COUNT (InventoryEvents.TimeCreated) AS [Total Inventory]
FROM
InventoryEvents
WHERE
InventoryEvents.TimeCreated between '1/1/2012' and '1/7/2012'
GROUP BY
InventoryEvents.UserIdCreated
****************************************************
SELECT
User.UserId
FROM
User
WHERE
User.Blocked = '0'
January 20, 2012 at 3:26 pm
Pretty sparse on details. Does this help?
SELECT AssetEvents.UserIdCreated,
COUNT (AssetEvents.TimeCreated) AS [Total Asset]
FROM AssetEvents
WHERE AssetEvents.TimeCreated between '1/1/2012' and '1/7/2012'
GROUP BY AssetEvents.UserIdCreated
union all
SELECT InventoryEvents.UserIdCreated,
COUNT (InventoryEvents.TimeCreated) AS [Total Inventory]
FROM InventoryEvents
WHERE InventoryEvents.TimeCreated between '1/1/2012' and '1/7/2012'
GROUP BY InventoryEvents.UserIdCreated
union all
SELECT User.UserId, NULL --need the null here so the columns are the same
FROM User
WHERE User.Blocked = '0'
If this isn't what you need you will need to provide some more details. ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. Take a look at the first link in my signature for best practices on posting questions to provide you the best chance of getting a good answer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 26, 2012 at 6:44 am
The UNION ALL won't help because it will take the name from the first SELECT as the column names.
Jenni, please post Create Table scripts with INSERTS that contain dummy data. Tell us what columns can be used between all tables to join them on and post the JOIN code you are using that is giving you incorrect results. Then tell us what the results should look like.
January 27, 2012 at 2:44 am
Hello jennigirl,
you coded three queries and now you only need to combine them using JOIN like that:
SELECT UserId, [Total Asset], [Total Inventory]
FROM User
LEFT JOIN (
SELECT UserIdCreated, COUNT (TimeCreated) AS [Total Asset]
FROM AssetEvents
WHERE TimeCreated between '1/1/2012' and '1/7/2012'
GROUP BY UserIdCreated
) AS AE ON AE.UserIdCreated = UserId
LEFT JOIN (
SELECT UserIdCreated, COUNT (TimeCreated) AS [Total Inventory]
FROM InventoryEvents
WHERE TimeCreated between '1/1/2012' and '1/7/2012'
GROUP BY UserIdCreated
) AS IE ON IE.UserIdCreated = UserId
WHERE Blocked = '0'
You said "but my joins are only giving me results from the Events table" but you doesn't show your queries to us!. If you publish your queries there is a chance that somebody can see some wrong code and hint you, please post your code if you suspect that something is wrong.
"and leaving out User Ids with no events against them", difficult to say without looking at your code but maybe you coded a INNER JOIN instead of a LEFT JOIN.
Hope this helps,
Francesc
January 27, 2012 at 5:03 am
Careful, Francesc. You're making assumptions of foreign key relations based on names that could or could not have anything to do with the actual UserID. I've seen a lot of databases that use the same (or similar) column names in multiple tables that actually cannot be joined upon because they aren't used that way.
For instance, the ever ubiquitous "id" column.
January 27, 2012 at 7:21 am
Brandie Tarvin (1/27/2012)
Careful, Francesc. You're making assumptions of foreign key relations based on names that could or could not have anything to do with the actual UserID. I've seen a lot of databases that use the same (or similar) column names in multiple tables that actually cannot be joined upon because they aren't used that way.For instance, the ever ubiquitous "id" column.
Ha ha, yes you are right, I answered in a risky way and I made assumptions that can be wrong. Maybe it is a part of this job; sometimes trying to understand and domain some code is like trying to map a room in the dark, extending the hand and discovering a needle because it hurts. Your previous answer was more careful than mine.
Best regards,
Francesc
January 27, 2012 at 7:23 am
frfernan (1/27/2012)
trying to understand and domain some code is like trying to map a room in the dark, extending the hand and discovering a needle because it hurts.
That is a beautiful answer. I love it!
February 13, 2012 at 11:42 pm
Heh... I map dark rooms with a flame thrower. Keeps me from pricking my finger on a needle. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply