Hi everybody,

 I have a problem in SQL server 2000 I have a stored procedure that returns loads of data for my CrystalReport in VB.NET

however I cant get over this problem

I have a table - tblConditions that looks lik this

DescId Description Condition
------------------------------------
   1 Raw State
   2 Cooked State
   3 Partly Cooked State
   4 Ambient Temp
   5 Frozen Temp
   6 Refrigerated Temp

Then I have another table - tblSamples looks like this

SampleId Name StateOfSample TempR TempS
------------------------------------------------------
   1 Chicken 1 4 5
   2 Beef 2 6 4
   3 Prawn 3 5 5


I need a SQL query that will return data from both of the tables so the row returned for SampleId = 2 will look like this:

SampleId Name StateOfSample TempR TempS
------------------------------------------------------
   2 Beef Raw Ambient Frozen


Does anybody have an idea how to do this in SQL statement?

You can JOIN tblConditions to tblSamples 3 times, you just need to use a different ON clause for each of the three JOINs, plus a table alias for at least 2 of them.

For example, the first JOIN will be ON StateOfSample=DescId, but the 2nd will be ON TempR=DescId.

Rather than me type it all out for you, just do it in Enterprise Manager (Open tblSamples using the Query option, then Add tblCondition three times and change the JOINS). EM will write the SQL for you, which you can save as a View or Stored Proc or whatever you want.

hth
Phil

Thanks I have tried that but got into this problem

The StateOfSample in tblSamples is type smallInt
and Decscription in tblConditions is type VarChar

and when I try to do what you said I am getting this error:

Syntax Error converting varChar Value 'Raw ' to a column of data
type smallInt

I have even tried to change the StateOfSample in tblSamples to VarChar but then I have no records returned at all...

Do you think you can write a bit of the query for mee so I can pick it up and wirte the rest of it myself?

:(

pajer,

I think it should be DESCRIPTION instead of DESCID there. Should have been a typo in Phil's case.;)

Cheers!

_________________________
- Vijay G
Strive for Perfection

Yeah thats what i have

SELECT *, tblConditions.Description AS Expr1
FROM tblSamples INNER JOIN
                      tblConditions ON tblSamples.StateOfSample = tblConditions.Description

returns an Empty resultset

No, not a typo. I said join it to DescID, not to Description.

Sorry phil, I just over looked that. That was my bad. you are right.

Pajer,
Phil's suggestion should have worked well. Please check your query.

_________________________
- Vijay G
Strive for Perfection

Try Not a great design to have tables joined on mismatched columns.

--

Joe

Yes guys I gor It working it creates a new Field Expr1 Expr2 and so on It took me while to get my head around it but I got it working finally Thanks...