Cannot figure out part of a joined sql

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

SQL Server 2000 Forums

Transact-SQL (2000)

Cannot figure out part of a joined sql

Author

Topic

gamaz2
Starting Member

31 Posts

Posted - 2008-07-28 : 15:53:18

Hi,
I have a view in sql server database where the sql statement is as follows
(multi joined sql)

SELECT PR.Project_Num AS ProjectNo, PR.Gen_Level, PR.Customer_ID,
PR.User_ID, PR.Project_Name, PR.Project_Desc, PR.Date_Submitted, PR.Date_Due,
PR.Date_Estimated, PR.Date_Completed, PR.Completed,
PR.Cust_PartDesc1, PR.Project_Status, PR.Eng_ID, PR.Cust_PartNum1 AS
ProjectPartNbr,
PR.Cust_PartDesc1 AS ProjectPartDesc,
CUST.Company_Name, CUST.City, CUST.State, CUST.Contact_Name, CUST.Phone_Num,
PP.Plant,
PL.Plant_Desc, PR.YearlyQty, C.aQty, SM.annVol1,
SM.accManager, U.Last_Name, U.First_Name
FROM prs.dbo.tblSMI SM LEFT OUTER JOIN
prs.dbo.Users U ON SM.accManager = U.User_ID RIGHT
OUTER JOIN
prs.dbo.Project PR LEFT OUTER JOIN
prs.dbo.tblCost C ON C.project_num = PR.Project_Num ON
SM.project_num = PR.Project_Num LEFT OUTER JOIN
prs.dbo.Customer CUST ON PR.Customer_ID =
CUST.Customer_ID LEFT OUTER JOIN
(SELECT Project_Num, Plant = MIN(Plant)
FROM PRS.dbo.Project_Plant PP2
GROUP BY Project_Num) PP ON PP.Project_Num =
PR.Project_Num LEFT OUTER JOIN
prs.dbo.Plants PL ON PL.Plant = PP.Plant

Now I need to change the join between SM and PR so that instead of = the
join contains the like statement

I have successfully done a prototype of this kind of join with two other
tables that reads as

SELECT top 100 tblEstimate.ProjectNo, SMIProject =
IsNULL(SMI.project_num,'NO SMI')
,tblEstimate.EstimateNo, tblEstimate.EstimateDesc, tblEstimate.EstimateTime
,SMI.annVol1
FROM tblEstimate
LEFT OUTER JOIN PRS.dbo.tblSMI SMI
ON tblEstimate.ProjectNo LIKE SMI.Project_Num + '%'
AND isnull(SMI.Project_Num,'') <> ''
ORDER BY ProjectNo, EstimateNo

However I am having difficulty to apply this exact concept in my sql code
where the joined entities are SM and PR that will be joined by project_num

Any help is appreciated. Thanks in advance.

Rekha
Starting Member

14 Posts

Posted - 2008-07-29 : 06:04:18

Can you please tell the issue or difficulty you are facing after applying new joins.
Attach the old and new code , this might help us figure what's going wrong

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources