Super Newbie wants to query show results from 2 (maybe 3 tables)

[remeron]

Just started learning PHP/MySQL for a week now, and I'm addicted!  I'm not a programmer or even related to computer by profession, so I kinda need some help here.

 

I have a table (tblinfo) which has (id, name, passport) and a second table (tbldrugs) which has (drugid, drugname).  tblinfo is linked to tbldrugs where each name in tblinfo can be taking a few drugs.  So how/what kind of sql query should i be writing so that whenever i query the tblinfo, it would show me the name and also the corresponding drug that person is taking from tbldrug.

 

Thanks!

[Chotor]

I'm a newbie myself, so beware of my answer, but I guess you need to JOIN the tables.

 

So something like:

SELECT drugname FROM tbldrugs JOIN tblinfo ON (tbldrug.drugid=tblinfo.id) WHERE tblinfo.name='insert name here'

Something like that. I'm not saying it's correct, but it can be a start. Someone else will surely give you the right query string.

[remeron]

thanks for pointing me to the right direction.  However i substituted the JOIN to LEFT JOIN instead and it worked wonderfully. 

However i'm in a bind now.  Currently i have these tables:

 

tblinfo = patient_id, patient_info

tbldrug1 = drug_id, patient_id, drugname  (LEFT JOIN tblinfo ON patient_id = tbldrug1.patient_id)

 

i could get mysql to query back results corresponding to the patient and which drug1 they are taking which is:

SELECT * FROM tblinfo LEFT JOIN tbldrug1 ON patient_id = tbldrug1.patient_id

 

now i want to another drug table which is tbldrug2:

 

tbldrug2 = drug2_id, patient_id, drug2name

 

How do i write the right string, whereby I the query would result in showing me, the patient info and the corresponding drug1 and drug2 they are taking?

 

can i write this:

 

SELECT * FROM tblinfo LEFT JOIN tbldrug1, tbldrug2 ON tblinfo.patient_id = tbldrug1.patient_id = tbldrug2.patient_id

[luca200]

You are totally out of the way, and Chotor did help you in this (he warned  you at least...)

Forget about tbldrug2, and forget about that silly join based on tbldrug.drugid=tblinfo.id.  It's totally senseless.

 

Think about this: any table has a primary key, that's identifiying that record in that table. So "id" in tblinfo represents people, while "drugid" in tbldrug represents a drug.

When you join two tables, you need two fields that have the same meaning. In this case, you could have a 'tblinfo_id' field in tbldrug table. But this were true if yours was a 1:N relation, meaning that every people could take just one drugs.

As this is not the case, your relation is a N:N relation, because every people can take many drugs. So you can't handle this with a tblinfo_id in tbldrug table (that would be a so-called 'foreign key'), but you'll need a 3rd table that you can call 'people_drugs', not working too much in fantasy  .

In this table, that can have e.g. 'tblinfo_id' and 'tbldrug_id',  you'll have a row for each people taking each drugs.