October 5, 2015 at 10:13 am
Hi all,
Not too sure if what I'm doing is possible. Or if it is, I'm going about it in completely the wrong way. Here is a sample dataset.
SELECT
SystemUserBase.FullName AS 'Engineer',
ActivityPointerBase.Description AS 'Work Detail',
ActivityPointerBase.RegardingObjectIdName AS 'Project Task No.',
new_projectsBase.new_ProjectName
FROM
SystemUserBase
INNER JOIN ActivityPointerBase
ON SystemUserBase.SystemUserID = ActivityPointerBase.OwnerID
INNER JOIN new_projectsBase
ON new_projecttasksBase.new_RelatedProjectId = new_projectsBase.new_projectsId
WHERE ActivityPointerBase.CreatedOn > DATEADD(Month, -1, getdate()) AND ActivityPointerBase.Subject = 'Project Task Work'
I can exaplin this more fully if needed, however the error I get when running this is the following:
The multipart identifier "new_projecttasksBase.new_RelatedProjectId" could not be bound.
I'm assuming the issue is with my JOINS. I've never been too good with those. The reason for the second one is to JOIN to another table (projectsBase) to pull the Project Name. I can pull the Project Task Number etc, but I need to pull the project title of the project that the tasks relate to.
I can post schema if it's needed. I understand it's not an ideal layout. But this is in a MS CRM system, and I'm essentially querying across multiple CRM Entities.
Thanks in advance for any advice. Cheers! 🙂
Dave
October 5, 2015 at 11:21 am
Do you have a new_RelatedProjectId column in your new_projecttasksBase table?
That's the error, not really a syntax error on the joins, probably a misspelled column name.
October 5, 2015 at 11:25 am
After reviewing the code, I found that you are referring new_projecttasksBase which isn't included in the query. If you're referencing it, you need to add it to the JOINS.
October 5, 2015 at 11:49 am
Quick question, can you post the DDL (create table script) for those two tables?
😎
October 5, 2015 at 1:56 pm
Luis Cazares (10/5/2015)
After reviewing the code, I found that you are referring new_projecttasksBase which isn't included in the query. If you're referencing it, you need to add it to the JOINS.
Hi Luis, Thanks for that. I've added it into the joins but am receiving 0 rows returned. Although the query now executes, so you were correct. I think I must be going about this in the wrong way.
October 5, 2015 at 2:53 pm
Hi Luis,
Sat down for half hour and re-wrote it all out as I was just approaching it all the wrong way. I ended up with the below that is working perfecly as I intended. Thanks for your help 🙂
SELECT
SystemUserBase.FullName AS 'Engineer',
ActivityPointerBase.Description AS 'Work Detail',
ActivityPointerBase.CreatedOn AS 'Work Added On',
new_projecttasksBase.new_projecttaskid AS 'Project Task No.',
new_projectsBase.new_projectid AS 'Project No.',
new_projectsBase.new_ProjectName AS 'Project Name'
FROM
SystemUserBase
INNER JOIN ActivityPointerBase ON SystemUserBase.SystemUserId = ActivityPointerBase.CreatedBy
INNER JOIN new_projecttasksBase ON ActivityPointerBase.RegardingObjectId = new_projecttasksBase.new_projecttasksId
INNER JOIN new_projectsBase ON new_projecttasksBase.new_RelatedProjectId = new_projectsBase.new_projectsId
WHERE ActivityPointerBase.CreatedOn > DATEADD(Month, -1, getdate()) AND ActivityPointerBase.Subject = 'Project Task Work'
October 8, 2015 at 7:16 am
I just wanted to add, as a suggestion, that you should use table alias in your queries. It will make your queries shorter and easier to identify column names. And by giving appropriate alias, you can identify the tables easily as well, in other words, don't use a, b, c...
SELECT
su.FullName AS 'Engineer',
ap.Description AS 'Work Detail',
ap.CreatedOn AS 'Work Added On',
npt.new_projecttaskid AS 'Project Task No.',
np.new_projectid AS 'Project No.',
np.new_ProjectName AS 'Project Name'
FROM SystemUserBase AS su
JOIN ActivityPointerBase AS ap ON su.SystemUserId = ap.CreatedBy
JOIN new_projecttasksBase AS npt ON ap.RegardingObjectId = npt.new_projecttasksId
JOIN new_projectsBase AS np ON npt.new_RelatedProjectId = np.new_projectsId
WHERE ap.CreatedOn > DATEADD(Month, -1, getdate())
AND ap.Subject = 'Project Task Work'
Can I ask why do your tables end with Base? Do all tables have that suffix or is it just a special kind of tables?
October 8, 2015 at 7:24 am
Luis Cazares (10/8/2015)
I just wanted to add, as a suggestion, that you should use table alias in your queries. It will make your queries shorter and easier to identify column names. And by giving appropriate alias, you can identify the tables easily as well, in other words, don't use a, b, c...
SELECT
su.FullName AS 'Engineer',
ap.Description AS 'Work Detail',
ap.CreatedOn AS 'Work Added On',
npt.new_projecttaskid AS 'Project Task No.',
np.new_projectid AS 'Project No.',
np.new_ProjectName AS 'Project Name'
FROM SystemUserBase AS su
JOIN ActivityPointerBase AS ap ON su.SystemUserId = ap.CreatedBy
JOIN new_projecttasksBase AS npt ON ap.RegardingObjectId = npt.new_projecttasksId
JOIN new_projectsBase AS np ON npt.new_RelatedProjectId = np.new_projectsId
WHERE ap.CreatedOn > DATEADD(Month, -1, getdate())
AND ap.Subject = 'Project Task Work'
Can I ask why do your tables end with Base? Do all tables have that suffix or is it just a special kind of tables?
Hi Luis, table alias' are a very good suggestion. I'll look at doing that from now on as its definitely easier to read. These queries re being run inside a relatively small report on an SSRS server, so load and repeated use isn't too mcuh of an issue but I do understand your point and will take that on board.
As for the Base suffix. The tables are all part of a Microsoft Dynamics CRM system. Each entity within CRM has two initial tables. One named the entity name, and one suffixed with "Base". Its a CRM topology thing, and the fields in each vary.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply