Results 1 to 3 of 3
Nov 19, 2004, 02:06 #1
- Join Date
- Sep 2003
- 0 Post(s)
- 0 Thread(s)
Grouping Data from a table that has 2 Foreign Keys
right, I have been trying to get to the point of this question for the last 2 weeks, but haven't really been able to put my question across correctly. Well now I think I may have enough knowledge of the actual requirement to my solution (I think ) to pose a clear question.
Ok, I have two tables, one that contains a list of hospitals, and one that contains a list of journey's in which a hospital has carried out (by tranferring patients). In my 'Journey's' table I have two columns that act as Foreign keys to the 'hospital' table. These two Foreign key based columns are 'fromhosp' and 'tohosp'. Now then, my query is to perform an accumilation of the journeys made by determining whether a journey was made to a hospital (which adds 1 to the hospital specified in the 'tohosp' column, and then if a journey was made from a hospital 1 will be added to the hospital in the 'fromhosp' column - these accumiltations are not a problem).
The problem lies in when I try to group the results by hospital, as there are 2 Foriegn keys that link to the same table (Hospitals table), and so when I attempt to make the link from the Journeys table to the 'Hospitals' table I end up linking to only on of the columns (for example the 'tohosp' column) and therefore will not get the results associated with the other column (for example the 'fromhosp' column).
I need to know if there is a way getting both these columns to link to the hospitals table so that I can produce a report that is consist for all the journeys in the 'Journeys' table.
I have been pulling my hair out over this for weeks , and would really appricate it if someone could at least point me in the right direction.
Nov 19, 2004, 02:36 #2
I don't know whether grouping would be possible, but if you e.g. just want to display both hospital names for both destination and place of departure, you can join the hospital table twice:
SELECT h1.name, h2.name, j.from, j.to FROM hospital AS h1, hospital AS h2, journey AS j WHERE j.from = h1.id AND j.to = h2.id AND ( j.from = '1' OR j.to = '1' )Never ascribe to malice,
that which can be explained by incompetence.
Your code should not look unmaintainable, just be that way.
Nov 19, 2004, 05:18 #3
- Join Date
- Jul 2002
- Toronto, Canada
- 49 Post(s)
- 1 Thread(s)
i'm pretty sure i saw swampboogie answer this already somewhereCode:
select Hospital , sum(trips) as total_trips from ( select Hospital , count(fromhosp) as trips from Journeys group by Hospital union all select Hospital , count(tohosp) as trips from Journeys group by Hospital ) dt group by Hospital