URGENT HELP

Question

Post reply

URGENT HELP

Best-330874

SSCrazy

Points: 2350

May 18, 2007 at 8:57 am

#120146

Hi,

i need help on this.

i want to write a stored procedure which Displays

School Name, Building Name, Building Type, Building Use from school and building table and

Assessed value and Assessed Date from Building Cost Table .

School Table	Building Table	Building Cost Table	District Table

School id	Building id	Building id	District id
School name	Building use	Assessed Value	Region id
LEA_Code	Building type	Assessed Date	LEA_Code
	Building Name
	School id
	LEA_Code

The results of that stored procedure will be as follows

School Name Building Name Building Type Building use Building Type Assessed value Assessed Date

042 Main Permanent Yes 1

052 Central Permanent Yes 1

062 Middle Permanent Yes 1

The columns Assessed value and Assessed date wil show no record

because there is no data for them in the table (Building cost tABLE)but still i need the two columns along with the others as stated above after the stored procedure is executed

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

What's your deal? You seem to post the same question multiple times in this forum. Why don't you just ask your instructor for help? People don't monitor these forums to do other's homework.

Greg

Best-330874 SSCrazy Points: 2350 More actions · Answer 2

IF YOU DONT WANT TO ANWSER SIMPLY DONT REPLY PLEASE OK .LET OTHERS HELP ME BECAUSE I GUESS THERE ARE STILL SOME PEOPLE IN THIS WORLD WHO HELP OTHERS WITHOUT TELLING LIKE YOU THAT IT IS A BIG DEAL THAT THEY KNOW THE ANSWER AND FYI IT IS NOT A HOMEWORK

tHANKS

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Best,

Usually people who are wanting answers to homework, supply the question and have done no work on their own. They want us to do all the work.

You must have done SOMETHING to try and resolve this. What have you come up with? We can look at that and tell you where you are going wrong or what you need to add/fix.

-SQLBill

Best-330874 SSCrazy Points: 2350 More actions · Answer 4

Hi,

i agree with you and i try to ask question only after iam stuck otherwise itry to resolve it myself becz i think that is the real learning

thanks

what happens here is in my select if i dont select the Assessed value from the table FMPS_Building_Cost it works fine AND DISPLAYS ME WHAT i need but when i Select Assessed Value as below from the FMPS tABLE IT TELLS ME THAT

0 rows affected.The reason is because there is no data in FMPS TABLE.

i want when i execute this statement it must show school name and other with the related data in those columns and the Assessed value with no value as there is no record in it.

CREATE

PROCEDURE FMPSGetFacilityDetail

(@LEA_Code varchar(4))

AS

Select

a.School_Name,

b

.Building_Name_Short,

c

.Building_Use,

d

.Building_Type,

e

.Assessed_Value,

From

APPN_School_List a,

APPN_Building_List b

,

Ref_Building_Use c

,

Ref_Building_Type d

,

FMPS_Building_Cost e

Where

a

.LEA_Code = @LEA_Code

And a.School_Id = b.School_Id

And b.Building_Id = e.Building_Id

And d.LEA_Code = @LEA_Code

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 5

I've looked through your previous posts and your questions are all over the place, but some appear to be more than typical homework and could be problems encountered at work, so I will assume this is for work for now....

Change your "select" statement inside your procedure around to use the prefered "Join" syntax on the "FROM" line like this:

Select <your columns here>

from schools s inner join building b on (s.school_id = b.school_id)

left outer join building_costs bc on (b.building_id = bc.building_id)

where s.lea_code = @lea_code

Now the explanation:

The inner join will retrieve only those schools that have buildings in the buildings table, (i'm assuming that is ok). The left outer join then says to report a value for each column of the building_cost table (referenced in the select portion) even if no matching record exists (NULL is used in that case)

Best-330874 SSCrazy Points: 2350 More actions · Answer 6

Best-330874

SSCrazy

Points: 2350

May 18, 2007 at 11:21 am

#707543

Thanks it worked

SQLBill SSC Guru Points: 51440 More actions · Answer 7

Also, be careful of these types of errors in your code:

e.Assessed_Value,

From APPN_School_List a,

Notice you have a comma after the last column and before the FROM. That will return a syntax error.

-SQLBill