SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Grouping Data from a table that has 2 Foreign Keys

    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

  2. #2
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm pretty sure i saw swampboogie answer this already somewhere
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •