SQL problem

Gman

Gman

Gman

Soldato
Joined
18 Oct 2002
Posts
4,925
Location
Yorkshire
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 ?
 
OK, firstly I'm going to question your database design before I can answer.

Why are there three tables: Practices, Schools and Centres with the same structure?
Wouldn't it have been better to have a single table with an identifier to say whether it is a Practice, School or Centre?

When you say the OrgCode can be 'P', 'S' or 'C' is this the code column in the three tables?
If not, where does this value come from, presumably there is a table describing what this value means?

If the database is rationalised in this way it would make querying it easier.
 
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
 
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.
 
oneilldo said:
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
Click to expand...
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.
 
I am sure you could do it with derived tables, case statements and outer joins.

Or you could leave it as multiple queries and put it in a Stored procedure (it may actually be faster)
 
Gman said:
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.
Click to expand...
Well, you didn't say that in the first post did you :p


Anyway, this should hopefully do what you want.
It's a little messy (and not particularly extendable if you add other tables similar to Practices etc.) but it should do the job.
Code: 
SELECT a.SequenceID
      ,a.appointmentTime
      ,a.CancelReason
      ,organisations.Description
FROM TempSchedule  ts
    ,Appointments  a
    ,(SELECT 'P' OrgCode
            ,p.Code
            ,p.Description
      FROM Practices p
      UNION ALL    
      SELECT 'S' OrgCode
            ,s.Code
            ,s.Description
      FROM Schools s
      UNION ALL 
      SELECT 'C' OrgCode
            ,c.Code
            ,c.Description
      FROM Centres c
      ) organisations
WHERE organisations.OrgCode = ts.OrgCode  
AND   ts.SequenceID  = a.SequenceID
AND   ts.ScheduleID  = ScheduleID

I've assumed TempSchedule joins with Appointments on both SequenceID and ScheduleID columns.
 
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
Code: 
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.
 
You must log in or register to reply here.
Back
Top Bottom