Populating values from different tables using query

Question

I have two tables Patients and Treatment type. For the past hour ive been trying to make a query to see the treatment type name each patient has had and groups Patient id 1 in one column and 2 in another column but instead of their id it will be their first name and last name.

I have posted an image mockup from excel of what i want the query to do below.

The name of the patients table is patients and the name of the treatment table is treatment_type

idPatients PatientFName PatientLName PatientDOB PatientsCounty PatieritsCity PatientsOccupation Specalist_idSpecialist
         1 Roger        James        16/05/1992 Hampshire      Portsmouth    Student            2
         2 James        Murray       08/02/1960 Middlesex      Harrow        Business Owner     1

https://i.stack.imgur.com/MKKBd.png

I hope someone can help me.

This is what id like it to look like: http://i.stack.imgur.com/ESHfH.png — Bobby Winter, Dec 07 '14 at 00:33
Please edit your question and include sample data and desired results in the question as text. You can format tables by putting four spaces at the beginning of the line or by highlighting lines and clicking on the "{}" button just over the edit window. — Gordon Linoff, Dec 07 '14 at 00:36
[Learn about SQL Joins](http://www.w3schools.com/sql/sql_join.asp) — Rafael, Dec 07 '14 at 00:43
Joins are what you need, if you provide a sqlfiddle schema I can give you back a query and explaination to help you understand. — OneHoopyFrood, Dec 07 '14 at 00:55
That's not exactly encouraging Bobby. If you want help then you should be willing to take the time now. Otherwise answerers feel unappreciated and appreciation is the only payment we receive. If you can't do this now then you'll have to solve it on your own. If I misinterpreted what you are saying and you _are_ doing it now then I apologize. — OneHoopyFrood, Dec 07 '14 at 01:04
You might also need pivot tables. Once you have the correct join, have a look to pivot tables here http://stackoverflow.com/questions/7674786/mysql-pivot-table — Carlos González, Dec 07 '14 at 01:47

OneHoopyFrood · Accepted Answer · 2014-12-07T02:35:02.677

I don't think you'll get exactly what you're wanting here, you can't create new columns for each patient the way you're thinking of.

You can however get a row for every condition that each patient has and include their name and id for each row. Then use your prepossessing language to detect when the ID is the same and list all conditions under that name in the format you're looking for. To do that you need a LEFT JOIN. Here's a great link to visualize what these can do for you:

http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

With what you're looking to do the statement will look something like this:

SELECT p.ID
       , CONCAT(p.FirstName, " ", p.LastName) AS PatientName 
       , t.TreatmentName
FROM   patients p
       LEFT JOIN treatment_type t ON p.ID = t.PatientID;

Here's an sqlfiddle to show that. The tables are named exactly like yours so you'll need to adjust them to fit your database. If you're just making this database you should really think about developing some kind of consistent naming convention, yours seem a bit scattered at the moment.

EDIT: As per carlosh12's comment on OP, you CAN do this kind of thing with a pivot table, but I'm not sure it's what you'd need for this situation. It's much faster and easier to just do it outside SQL as detailed here.

EDIT 2 Sorry, forgot to link to my SQLfiddle. It's there now. Here is an edit where I included an ORDER BY statement to answer your comment.

This is great! If i did want to organise it by ascending name, would i just add an ascending at the end of the code? or would it be in a group?Edit// Dont worry managed to do this. — Bobby Winter, Dec 07 '14 at 02:31
Thanks for your help i see how to use fiddle now and LEFTJoins while reading up on it. Thanks a lot — Bobby Winter, Dec 07 '14 at 02:37

Populating values from different tables using query

1 Answers1