Need Help

[gjmac2]

I am trying to pull certain cases from a table, and am not sure how to do this.

Here is my query that I am running:

SELECT TOP (100) PERCENT dbo.VW_Triage.CaseID, dbrganTissueGroup.Category, dbrganTissueGroup.OrganTissue AS Expr1
FROM Tissue.RecoveryTissue AS TISSUE INNER JOIN
dbo.VW_Triage ON TISSUE.DonorTracCaseId = dbo.VW_Triage.DonorTracCaseID LEFT OUTER JOIN
dbrganTissueGroup ON TISSUE.TissueId = dbrganTissueGroup.ID
GROUP BY dbo.VW_Triage.CaseID, dbrganTissueGroup.Category, dbrganTissueGroup.OrganTissue
ORDER BY dbo.VW_Triage.CaseID

I am trying to return only those caseid's that have a certain value only in the OrganTissue field. Any help would be much appreciated.

 

[bborissov]

SELECT VW_Triage.CaseID,
       OrganTissueGroup.Category,
       OrganTissueGroup.OrganTissue AS Expr1
FROM Tissue.RecoveryTissue  TISSUE
INNER JOIN dbo.VW_Triage        VW_Triage        ON TISSUE.DonorTracCaseId = VW_Triage.DonorTracCaseID
INNER JOIN dbo.OrganTissueGroup OrganTissueGroup ON TISSUE.TissueId        = OrganTissueGroup.ID
WHERE  OrganTissueGroup.OrganTissue IN (...put values here...)
GROUP BY VW_Triage.CaseID, 
         OrganTissueGroup.Category,
         OrganTissueGroup.OrganTissue
ORDER BY VW_Triage.CaseID

or if you really need to do a LEFT JOIN:

SELECT VW_Triage.CaseID,
       OrganTissueGroup.Category,
       OrganTissueGroup.OrganTissue AS Expr1
FROM Tissue.RecoveryTissue  TISSUE
INNER JOIN dbo.VW_Triage        VW_Triage       ON TISSUE.DonorTracCaseId = VW_Triage.DonorTracCaseID
LEFT JOIN dbo.OrganTissueGroup OrganTissueGroup ON TISSUE.TissueId        = OrganTissueGroup.ID  
                                                AND OrganTissueGroup.OrganTissue IN (...put values here...)
GROUP BY VW_Triage.CaseID, 
         OrganTissueGroup.Category,
         OrganTissueGroup.OrganTissue
ORDER BY VW_Triage.CaseID

Borislav Borissov
VFP9 SP2, SQL Server

 

[flapeyre]

Since you have a [tt]GROUP BY[/tt] clause, you need to use a [tt]HAVING[/tt] clause:

[COLOR=#0000FF]SELECT[/color] [COLOR=#0000FF]TOP[/color] ([COLOR=#FF0000]100[/color]) [COLOR=#0000FF]PERCENT[/color] dbo.VW_Triage.CaseID, dbo.OrganTissueGroup.Category, dbo.OrganTissueGroup.OrganTissue [COLOR=#0000FF]AS[/color] Expr1
[COLOR=#0000FF]FROM[/color] Tissue.RecoveryTissue [COLOR=#0000FF]AS[/color] TISSUE [COLOR=#0000FF]INNER[/color] [COLOR=#FF0000]JOIN[/color]
dbo.VW_Triage [COLOR=#0000FF]ON[/color] TISSUE.DonorTracCaseId = dbo.VW_Triage.DonorTracCaseID [COLOR=#00B0B0]LEFT[/color] [COLOR=#FF0000]OUTER[/color] [COLOR=#FF0000]JOIN[/color]
dbo.OrganTissueGroup [COLOR=#0000FF]ON[/color] TISSUE.TissueId = dbo.OrganTissueGroup.ID
[COLOR=#0000FF]GROUP[/color] [COLOR=#0000FF]BY[/color] dbo.VW_Triage.CaseID, dbo.OrganTissueGroup.Category, dbo.OrganTissueGroup.OrganTissue
[COLOR=#0000FF][highlight #FCE94F]HAVING dbo.OrganTissueGroup.OrganTissue = @value[/highlight][/color]
[COLOR=#0000FF]ORDER[/color] [COLOR=#0000FF]BY[/color] dbo.VW_Triage.CaseID

-- Francis
There are laws to protect the freedom of the press's speech, but none that are worth anything to protect the people from the press.
--Mark Twain

 

[flapeyre]

Boris's solution will work if you are looking for multiple values which are comma-delimited. Mine should work for a single value.

-- Francis
There are laws to protect the freedom of the press's speech, but none that are worth anything to protect the people from the press.
--Mark Twain

 

[bborissov]

There is need of HAVING, just change WHERE OrganTissueGroup.OrganTissue IN (...put values here...) to WHERE OrganTissueGroup.OrganTissue = single_value
HAVING is mostly used to filter the query AFTER GROUP BY and when you want to use the result of aggregate function as a filter:

SELECT Field1, Field2
FROM SomeTable
GROUP BY Field1, Field2
HAVING COUNT(*) > 1

BTW why you have GROUP BY?
You didn't have any aggregate function in the query?

Borislav Borissov
VFP9 SP2, SQL Server

 

[gmmastros]

Boris,

I'm guessing 1 of 2 things....

1. Group by without any aggregates is the same as a distinct.

2. Just like the "Top (100) Percent", this person may be in the habit of adding it to every query. Probably because it solved a problem once.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gjmac2]

Thank you all for the help. I took your suggestions a tweeted tham a bit, but it is working. As you can probably guess, I am not very proficient at SQl.

Thank you all again for your help. I really appreciate it.