SQL problem

[Gman]

got a slight problem where trying to work out how to do this sql.

tables are:

Appointments
------------
SequenceID | ScheduleID | ClientID | appointmentTime | CancelReason


TempSchedule
---------------
SequenceID | ScheduleID | ScheduleDate | OrganisationType | OrgCode


Practices
-------------
Code | Description


Schools
------------
Code | Description


Centres
------------
Code | Description



I need to return the following

appointments.SequenceID
appointments.appointmentTime
appointments.cancelReason
AND the description of the place the appointments at.

Now the description is the problem, In the TempSchedule table it holds the Organisation code which can be 'P'
for practice 'S' for school or 'C' for center. this code p/s/c will tell the sql which one the three tables it
needs to get the Descripition out of. It then needs to find the description in that table which matches the
OrgCode in the TempSchedule table.

Now I can do what i'm wanting in seperate sql statements but tring to do it all in one

Any ideas how I go about getting the Description part ?

 

[Gman]

The table structure as it is must remain as its out of my control.

I agree there should be some mapping somewhere that dictates what this 'p', 's' and 'c' relate to but I've yet to find it.

as for why is there three tables, i'd say because they are all separate entities with vastly different attributes i.e. a practice will contain different things to a school etc. the only common facter I can see is the Fields 'Code' and 'CodeDescription' were code is a unique code for the school, practice etc and the codeDescription is the full name of the school or practice.

will query the 'p', 's' and 'c' entries tomorrow as like you they seem stupid unless theres some table that these are keys of.

 

[Gman]

As already said it makes no sense to have a different table for the organisation types in this case, they should all really be in one table
In fact most of the schema you have posted seems rather questionable

A quick frig

SELECT Code, Description, CONVERT(char(1),'P') as OrganistationType INTO Organistations
FROM Practices

INSERT INTO Organistations
SELECT Code, Description, 'S' FROM Schools

INSERT INTO Organistations
SELECT Code, Description, 'C' FROM Centres

Now the SQL

SELECT a.SequenceID, a.appointmentTime, a.cancelReason, o.Description
FROM Appointments a, TempSchedule t, Organisations o
WHERE a.ScheduleID = t.ScheduleID --I am guessing this is the relation though both columns have same name?
AND t.OrgType = o.OrganistationType
AND t.OrgCode = o.Description

but that's not all in one query was thinking about some kind of nested statement.

at the moment i'm doing it by getting the ScheduleID from the Appointments table based on the date and time of the appointment. I can then use this ScheduleID in a join to get the OrganisationType and OrgCode out of the TempSchedule table. I've then got a case statement to convert the 'p' , 's', 'c' into there respective table names.
The correct table name and orgCode is placed ina variable which i'm then using in the other query to get the appointment details I need.

The trick is finding some way of combining these two parts into one query.

 

[Gman]

Thanks mate gave me something to work off.

Ended up doing a Left Join on all three of the tables (practice, school and centre) then in the select I did a case statement like so

CASE WHEN ggp.CodeDescription IS NOT NULL THEN ggp.CodeDescription
	 WHEN gcc.CodeDescription IS NOT NULL THEN gcc.CodeDescription
	 WHEN gs.CodeDescription IS  NOT NULL THEN gs.CodeDescription
END

Which seems to work so long as the OrgCode in the Schedule is unique i.e. a school and a practice doesn't have exactly the same OrgCode.