MySQL - How to select Multiple rows from Multiple tables

Question

I have a question about a SQL statement, for some reason my script is not working properly.. This is the Scenario; I have 6 tables

Patient
-PatientID
-PatientName
-EmployerID FK to employer.EmployerID.

Employer
-EmployerID
-EmployerName

Carrier
-CarrierID
-CarrierName

Appointment
-AppointmentID
-AptDateTime
-PatientID FK to patient.PatientID

InsurancePlan
-PlanID
-GroupName
-EmployerID FK to employer.EmployerID
-CarrierID FK to carrier.CarrierID

Inssub
-InsubID
-DateEffective
-PlanID FK to insplan.PlanID
-Suscriber FK to patient.PatientID

My Script: I need get all the rows from those 5 tables. I am not really good for SQL Indexes validation, that's why my script is not working properly !

SELECT p.PatientName, e.EmployerName, c.CarrierName, ip.GroupName, a.AptDateTime, i.DateEffective

FROM patient p, employer e, inssub i, InsurancePlan ip, carrier c, appointment a

WHERE e.EmployerNum = p.EmployerNum AND 
      i.Subscriber = p.PatientID AND
      i.PlanID = ip.PlanID AND
      ip.CarrierID = c.CarrierID AND
      ip.employerID = e.EmployerID AND
      ip.PlanID = i.PlanID AND
          a.PatientID = p.PatientID AND
          a.DateTStamp > '2013/01/01' AND    
      a.AptDateTime != '0001-01-01 00:00:00'

PinnyM · Accepted Answer · 2013-05-21 14:16:21Z

3

It would be simpler if you use explicit JOINS rather than comma-delimited tables:

SELECT p.PatientName, e.EmployerName, c.CarrierName, ip.GroupName, a.AptDateTime, i.DateEffective
FROM patient p
JOIN employer e ON p.EmployerID = e.EmployerID
JOIN insuranceplan ip ON e.EmployerID = ip.EmployerID
JOIN carrier c ON ip.CarrierID = c.CarrierID
JOIN appointment a ON p.PatientID = a.PatientID
JOIN inssub i ON p.PatientID = i.Subscriber AND ip.PlanID = i.PlanID
WHERE a.DateTStamp > '2013/01/01'
  AND a.AptDateTime != '0001-01-01 00:00:00'

edited May 21, 2013 at 14:16

answered May 21, 2013 at 3:08

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PinnyM Over a year ago

@Jay, Updated to note additional column. If this doesn't do what you need, can you post some sample data, and what result you are expecting to see?

sentil kumar · Accepted Answer · 2013-05-21 03:17:57Z

0

SELECT p.PatientName, e.EmployerName, c.CarrierName, ip.GroupName, a.AptDateTime
FROM Patient p JOIN Employer e ON p.EmployerId=e.EmployerId
JOIN Appointment a ON p.PatientId= a.PatientId
JOIN InsurancePlan ip ON ip.EmployerId=e.EmployerId
JOIN Carrier c ON c.CarrierId=ip.CarrierId
Join Inssub i ON i.PlanId=ip.PlanId
WHERE a.DateTStamp > '2013/01/01'
AND a.AptDateTime != '0001-01-01 00:00:00'

Try this and see

answered May 21, 2013 at 3:17

sentil kumar

952 silver badges7 bronze badges

6 Comments

PinnyM Over a year ago

Missing employer join. And inssub isn't really needed for the SELECT used. Are you adding something that I missed?

sentil kumar Over a year ago

I include the employer in very first statement it self.

PinnyM Over a year ago

Missed that - my apologies. But the inssub table shouldn't be needed here.

sentil kumar Over a year ago

No prob bro. It might can't see clearly because it put it together.

Jay Over a year ago

Sorry was my mistake,i missed the inssub's DateEffective on select statement (updated) i've tried this script, but still not getting all the records i have on database,. i have 3 in total, just returned 2

|

Collectives™ on Stack Overflow

MySQL - How to select Multiple rows from Multiple tables

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related