php sql two tables joins help

[thetylercox]

I have 2 tables

 

Table name Dist.

NAME|Phone|ID

----------------

Oakley|555-555|1

Maui|666-666|2

lux|777-7777|3

 

Table name Patientacct.

Name|prescription|id|dist

-------------------------------

bob|20-20|1|oakley

billy|15-20|2|Oakley, Maui

kim|20-20|3|Lux

 

 

Im looking for a display like

 

Oakley

--------------------

Bob

Billy

 

Maui

------------------

Billy

 

Lux

--------------

kim

 

Trials so far

 

SELECT * FROM `dist` JOIN `patientacct` ON patientacct.dist LIKE CONCAT('%', `dist`.name ,'%') GROUP BY `dist`.name

 

This showed only 1 dist if i drop the group by example:SELECT * FROM `dist` JOIN `patientacct` ON patientacct.dist LIKE CONCAT('%', `dist`.name ,'%') i get the record twice so what I need is somewhere between the two. Im extremely new to joins so be easy when explaining. Thanks

[Drummin]

Have you tried LEFT JOIN?

[thetylercox]

as i said im entirely new to the idea of joins. I have not tried any other join than the one i listed. I have been trying to read but its a little difficult to wrap my head around at the moment. How would a left join differ? Whats the querry sintax u would suggest?

[thetylercox]

how about an easier question how could i link these tables "relational" when one record can be tied to one or more other records. As above oakley is tied to bob and billy,

[thetylercox]

how to get from this to this is the question!

[Drummin]

You should move away from using a name to tie these records together.  I assume that id is a primary key in the Dist table.  In all other tables that would tie with this table, there should be a column that holds this id like dist_id.

 

$sql="SELECT d.name, d.phone, p.pame, p.prescription, p.id  
FROM `dist` AS d 
LEFT JOIN `patientacct` AS p 
ON d.id = p.dist_id
GROUP BY d.name";

 

This should pull up each district and then list any patient account data that has the dist_id, then the next record etc.

[Drummin]

I wanted to also point you to this post regarding DB structure.  This is an important concept to understand when starting out.

http://forums.phpfreaks.com/index.php?topic=361424.msg1710272#msg1710272