SELECT STATEMENT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT STATEMENT

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-04-29 : 10:48:03

Hi,

I have two select statement, the first generates the projectname as shown below - this generates 12 records.


SELECT     TOP (100) PERCENT ProjectID, ISACProtocol + ' - ' + ProjectDesc AS ProjectName
FROM         dbo.tblProjects
WHERE     (ProjectStatus = '0')
GROUP BY ProjectID, ISACProtocol, ProjectDesc
ORDER BY ISACProtocol, ProjectDesc

Then I have this select statement which reads the projectname from the above select statement as shown

SELECT     TOP (100) PERCENT dbo.tblProjects.ProjectID, dbo.qryactiveprojects.ProjectName, dbo.tblProjects.ProjectDesc, 
                      dbo.tbl_Enquiries.Initial + ' ' + dbo.tbl_Enquiries.Surname AS Clientname, dbo.tbl_Organisation.OrgName, dbo.tbl_PLDStaff.StaffName, 
                      dbo.tblProjects.StartDt, dbo.tblProjects.CompletionDt, dbo.tblProjects.FullFee, dbo.tblProjects.PartFee, dbo.tblProjects.ISACProtocol, 
                      dbo.tblProjectTypes.ProjectType
FROM         dbo.tblProjects INNER JOIN
                      dbo.tbl_Organisation ON dbo.tblProjects.ClientOrg_OrganisationID = dbo.tbl_Organisation.OrgID INNER JOIN
                      dbo.tbl_PLDStaff ON dbo.tblProjects.LeadPerson_StaffID = dbo.tbl_PLDStaff.StaffID INNER JOIN
                      dbo.tbl_Enquiries ON dbo.tblProjects.ClientNme_EnquirerID = dbo.tbl_Enquiries.EnquirerID INNER JOIN
                      dbo.qryactiveprojects ON dbo.tblProjects.ProjectID = dbo.qryactiveprojects.ProjectID INNER JOIN
                      dbo.tblProjectTypes ON dbo.tblProjects.ProjectTypeID = dbo.tblProjectTypes.ProjectTypeID
ORDER BY dbo.qryactiveprojects.ProjectName

This brings 8 records and 4 project names are not shown, please could anyone help me..

Thanks

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-29 : 11:03:27

First of all, lets start by re-organizing your query a bit:

SELECT *
FROM         dbo.tblProjects 
INNER JOIN	dbo.tbl_Organisation ON dbo.tblProjects.ClientOrg_OrganisationID = dbo.tbl_Organisation.OrgID 
INNER JOIN	dbo.tbl_PLDStaff ON dbo.tblProjects.LeadPerson_StaffID = dbo.tbl_PLDStaff.StaffID 
INNER JOIN	dbo.tbl_Enquiries ON dbo.tblProjects.ClientNme_EnquirerID = dbo.tbl_Enquiries.EnquirerID 
INNER JOIN	dbo.qryactiveprojects ON dbo.tblProjects.ProjectID = dbo.qryactiveprojects.ProjectID 
INNER JOIN	dbo.tblProjectTypes ON dbo.tblProjects.ProjectTypeID = dbo.tblProjectTypes.ProjectTypeID
ORDER BY dbo.qryactiveprojects.ProjectName

Now you can run this query, commenting out each join one at a time to determine which join is filtering out the records.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-04-29 : 11:09:47

when I ran this query I still get 8 records

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-04-29 : 11:10:33

What is dbo.qryactiveprojects ? Is that the result of the first SELECT?

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-04-29 : 11:14:19

yes

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-04-29 : 11:17:55

Seems redundant to me...can u try this?

SELECT     TOP (100) PERCENT dbo.tblProjects.ProjectID, dbo.tblProjects.ISACProtocol + ' - ' + dbo.tblProjects.ProjectDesc AS ProjectName , dbo.tblProjects.ProjectDesc, 
                      dbo.tbl_Enquiries.Initial + ' ' + dbo.tbl_Enquiries.Surname AS Clientname, dbo.tbl_Organisation.OrgName, dbo.tbl_PLDStaff.StaffName, 
                      dbo.tblProjects.StartDt, dbo.tblProjects.CompletionDt, dbo.tblProjects.FullFee, dbo.tblProjects.PartFee, dbo.tblProjects.ISACProtocol, 
                      dbo.tblProjectTypes.ProjectType
FROM         dbo.tblProjects INNER JOIN
                      dbo.tbl_Organisation ON dbo.tblProjects.ClientOrg_OrganisationID = dbo.tbl_Organisation.OrgID INNER JOIN
                      dbo.tbl_PLDStaff ON dbo.tblProjects.LeadPerson_StaffID = dbo.tbl_PLDStaff.StaffID INNER JOIN
                      dbo.tbl_Enquiries ON dbo.tblProjects.ClientNme_EnquirerID = dbo.tbl_Enquiries.EnquirerID INNER JOIN
                      dbo.tblProjectTypes ON dbo.tblProjects.ProjectTypeID = dbo.tblProjectTypes.ProjectTypeID
where dbo.tblProjects.ProjectStatus = '0'

EDIT: I wouldn't think this would take care of your problem which is of course, because of the INNER JOINS like "DBA in the making" suggested. Try changing them to LEFT JOINS and run the query.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-04-29 : 11:20:58

never worked

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-04-29 : 11:22:21

What didn't work? Did you try removing the JOINs one by one? Or did you try using LEFT JOINs. I'm not sure what suggestion you tried.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-04-29 : 11:29:53

changed all inner joins to left joins and it brought 87 records

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-04-29 : 11:46:22

Thank you guys it worked for me by chaning the one of the inner join to left join as shown below

SELECT TOP (100) PERCENT dbo.tblProjects.ProjectID, dbo.qryactiveprojects.ProjectName, dbo.tblProjects.ProjectDesc,
dbo.tbl_Enquiries.Initial + ' ' + dbo.tbl_Enquiries.Surname AS Clientname, dbo.tbl_Organisation.OrgName, dbo.tbl_PLDStaff.StaffName,
dbo.tblProjects.StartDt, dbo.tblProjects.CompletionDt, dbo.tblProjects.FullFee, dbo.tblProjects.PartFee, dbo.tblProjects.ISACProtocol,
dbo.tblProjectTypes.ProjectType
FROM dbo.tblProjects INNER JOIN
dbo.tbl_Organisation ON dbo.tblProjects.ClientOrg_OrganisationID = dbo.tbl_Organisation.OrgID LEFT JOIN
dbo.tbl_PLDStaff ON dbo.tblProjects.LeadPerson_StaffID = dbo.tbl_PLDStaff.StaffID INNER JOIN
dbo.tbl_Enquiries ON dbo.tblProjects.ClientNme_EnquirerID = dbo.tbl_Enquiries.EnquirerID INNER JOIN
dbo.qryactiveprojects ON dbo.tblProjects.ProjectID = dbo.qryactiveprojects.ProjectID INNER JOIN
dbo.tblProjectTypes ON dbo.tblProjects.ProjectTypeID = dbo.tblProjectTypes.ProjectTypeID
ORDER BY dbo.qryactiveprojects.ProjectName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources