Showing posts with label syntaxselect. Show all posts
Showing posts with label syntaxselect. Show all posts

Friday, March 30, 2012

Multiple Joins - Need Help

have the following code for ONE Inner Join, but I want to add another join for another Table and Fields... can you help me with the syntax:

SELECT DISTINCT

CTR.ReqID, CTR.SpecimenID, CTR.LabID, CTR.ProcedureID, CTR.TestID, CTR.Isolate, CTR.Problem,

CTR.ProblemComments, CP.ProcedureID, CP.Description AS CPProcedureDescription


FROM ClinicalTestsRequested CTR inner join ClinicalProcedures CP

ON CTR.ProcedureID=CP.ProcedureID


WHERE (CTR.SpecimenID = @.Accession)

I want to add another Join to the above where:

Table = ClinicalTests CT
Fields = CT.TestID, CT.Description AS CTTestDescription

and Compare = CTR.TestID to CT.TestID

Thanks !!

after your on clause for the first inner join add:

inner join clinicalTests ct on ctr.testid = ct.testid

add your columns to the list in the select.

Better yet, use sql server management studio express to design the query for you (it doensn't matter if your database is sql2005 or 2000). Create a new query for one of your tables and then use the query designer. This will allow you to drag and drop joins, make them into outer joins, add columns, etc, using a design tool.

--JJ

Friday, March 9, 2012

Multiple Contain Statements

I need some help with this syntax
Select * FROM PROJECTS
WHERE CONTAINS(Problem_Description, '"Invalid use of null"') And WHERE
CONTAINS(Problem_Status, '"Open"')
Can you have multiple CONTAINS Statements?
I am getting a syntax error with the above code snippet. What am I missing?
Thanks!You have an extra WHERE clause. CONTAINS is a predicate, and you can
logically combine predicates using AND or OR; you only need one WHERE
clause:
WHERE CONTAINS(x, 'abc') AND CONTAINS(y, 'def')
But in your case, I'm wondering if you really want to use full-text search
for your problem status column? Will the status really be more than one
word? You'd probably have much better luck using:
WHERE CONTAINS(Problem_Description, '"Invalid use of null"')
AND Problem_Status = 'Open'
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:95B2EA60-F534-46EA-A4F4-C39A447C5FC9@.microsoft.com...
>I need some help with this syntax
> Select * FROM PROJECTS
> WHERE CONTAINS(Problem_Description, '"Invalid use of null"') And WHERE
> CONTAINS(Problem_Status, '"Open"')
> Can you have multiple CONTAINS Statements?
> I am getting a syntax error with the above code snippet. What am I
> missing?
> Thanks!
>|||Thanks for your help!
"Adam Machanic" wrote:

> You have an extra WHERE clause. CONTAINS is a predicate, and you can
> logically combine predicates using AND or OR; you only need one WHERE
> clause:
> WHERE CONTAINS(x, 'abc') AND CONTAINS(y, 'def')
> But in your case, I'm wondering if you really want to use full-text search
> for your problem status column? Will the status really be more than one
> word? You'd probably have much better luck using:
> WHERE CONTAINS(Problem_Description, '"Invalid use of null"')
> AND Problem_Status = 'Open'
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
> news:95B2EA60-F534-46EA-A4F4-C39A447C5FC9@.microsoft.com...
>
>