Databases
StackOverflow2013
Postsdbo
POHow to get records out of multiple Tables with messy relationship
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to get records out of multiple Tables with messy relationship
    primarykey
    data
    text
    <p><img src="https://i.stack.imgur.com/0bmhP.png" alt="enter image description here"></p> <p>Hello! I've been working to get records out of this schema, really isn't working out for me.. The relation is so messy</p> <p>PatPlan table's Ordinal field is an INT (1,2)<br> 1= primary insurance<br> 2= Secondary Insurance</p> <p>and relation field(the relationship between the patient who has the primary insurance and the patient who has the appointment) works the same way<br> 1= self<br> 2= spouse<br> 3= Child </p> <p>and the inssub Table Subscriber is a Patient</p> <p>I need to get basically 3 big categories :</p> <pre><code>1. Appointment's Fields -AptDateTime -DateTStamp -Patient's FirstName -Patient's LastName -Patient's Gender -Patient's Position -Patient's EmployerName 2. Guarantor's fields //Same as the Patient's fields (Guarantor is a patient) //Must be the guarantor related to the patient who has the appointment -Patient's FirstName -Patient's LastName -Patient's Gender -Patient's Position -Patient's EmployerName -CarrierName -EmployerName 3. Primary Insurance (This is the one i can't work it out) //Primary insurance is as same as the patient's table -Patient's FirstName -Patient's LastName -Patient's Gender -Patient's Position -Patient's EmployerName -CarrierName -EmployerName </code></pre> <p>I will appreciate your help, as I've been working on this 3 days, and I haven't solve it yet.</p> <p>My current Query:</p> <pre><code>SELECT p.Title, p.FName, p.LName, p.Position, p.Gender,e.EmpName, pm.FName AS PMFname, pm.LName AS PMLName, pp.Relationship AS PMPatStatus, pme.EmpName AS PMEmpName, i.DateEffective AS PMDateEffective, i.DateTerm AS PMDateTerm, c.CarrierName AS PMCarrierName, pme.EmpName AS PMEmpName, g.FName AS GFName, g.LName AS GLName, pp.Relationship AS GPatStatus, g.Gender AS GGender,ge.EmpName AS GEmpName, a.AptDateTime, a.AptNum, a.PatNum FROM patient p LEFT OUTER JOIN Employer e ON p.employerNum = e.employerNum inner JOIN Appointment a ON a.PatNum = p.PatNum left JOIN Insplan ip ON ip.EmployerNum = e.employerNum left JOIN Carrier c ON c.carrierNum = ip.carrierNum left JOIN Inssub i ON i.PlanNum = ip.PlanNum left JOIN patplan pp ON pp.InsSubNum = i.InsSubNum AND pp.PatNum = p.PatNum left JOIN Patient pm ON pm.PatNum = i.subscriber left JOIN Employer pme ON pme.employerNum = pm.employerNum left JOIN Patient g ON g.PatNum = p.Guarantor LEFT JOIN Employer ge ON ge.employerNum = g.employerNum WHERE a.DateTStamp &gt; '2012/06/03' AND a.AptDateTime != '0001-01-01 00:00:00' </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USJay
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COWhat are the queries that you DO have?
      singulars
      1. POHow to get records out of multiple Tables with messy relationship
      1. USSgarz
    2. COJust updated my current query. Thank You
      singulars
      1. POHow to get records out of multiple Tables with messy relationship
      1. USJay
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload