Grouping Data from a table that has 2 Foreign Keys

[Tryst]

Hi all,

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.

Thanks

Tryst

[kleineme]

Hi,

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' )

[r937]

i'm pretty sure i saw swampboogie answer this already somewhere

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

Edit: oh yeah, here: http://www.sitepoint.com/forums/showthread.php?t=210845