[SOLVED] Inner Join HELP!

[benphp]

This will be simple for you SQL masters. I have three tables:

 

tbl: name

uid  name 

1 John Did

2 Greek Tor

3 Alex John

 

tbl: task

uid task

2 Training

1 Development

1 Marketing

1 Testing

3 Support

3 Sales

 

tbl: dept

uid dept

1 Dept A

2 Dept B

2 Dept C

3 Dept D

 

Each person has multiple tasks and departments. I want to query on all tables:

SELECT name.name, name.uid FROM names WHERE name.name LIKE '%term%' OR task.task LIKE '%term%' OR dept.dept LIKE '%term%'

 

I've tried different JOINs and cant' get it to work.

 

Any ideas?

 

Thanks!

[php_dave]

Hello mate,

 

If these are your reference tables then firstly you need to have unique identifiers in uid for each row - and then use a link table to join them togethor.

 

Link Table:

link_id:    link_name:      link_dept:    link_task:

1                1                  1                1

 

Then this below query will give you what you want

 

SELECT name.name, dept.dept, task.task
from
link_id
Inner Join name on link_name=name.uid
Inner Join dept  on link_dept=dept.uid
Inner Join task on link_task=task.uid
where <conditions here>

 

HTH

 

Dave

 

[Barand]

SELECT DISTINCT n.name, n.uid 
FROM names n 
JOIN depts d ON n.uid = d.uid
JOIN tasks t ON n.uid = t.uid
WHERE (n.name LIKE '%term%') OR (t.task LIKE '%term%') OR (d.dept LIKE '%term%')

[benphp]

Thank you!

 

You guys make it look easy.

 

I used Barand's and added LEFT JOIN, and it works!

 

SELECT DISTINCT n.name, n.uid

FROM names n

LEFT JOIN depts d ON n.uid = d.uid

LEFT JOIN tasks t ON n.uid = t.uid

WHERE (n.name LIKE '%term%') OR (t.task LIKE '%term%') OR (d.dept LIKE '%term%')