How do I find matching records from two database table?

[sun1967]

Hi,
I am new to web development.I am trying to create a web application for finding project match. There are two types of users for the site - student and supervisers. Supervisers can post projects that requires more than one expertise. Students can post their CVs with more than one expertise. In my datbase tables I have two tables student_expertise table with student_id and expertise_id & project_experise table with project_id and expertise_id. How do I get a student_id that matches with a project with same expertise?

Hope somebody will be able to help.

Thanks,
Sun67

[RonPK]

Hello sun67, welcome to WebmasterWorld!

Your question is about how to query a database, so I'm not sure whether this is the right forum...

Anyway, presuming your database supports some sort of SQL, try something like this:

SELECT student_id 
 FROM student_expertise, project_expertise 
 WHERE student_expertise.expertise_id = project_expertise.expertise_id

If this doesn't work, I suggest you read the section on JOINs in your database's manual.

[sun1967]

Thanks to your reply RonPK. I know it is not the right forum. But I couldn't find anything more appropriate.

Now about the query - The problem is there will be more than one records in both tables which satisfy the conditions. So in my head the only way I could compare them is by extracting the values from both table and then populating them to a multiple column array and then do an array comparison. But I have no idea how to do the coding. If anybody know please help.

Thanks.
Sun67

[RonPK]

Hmmm forget my first reply - must have been half asleep...

To get things clear, is this what your tables look like?
table student_expertise (students can have more than 1 expertise):

student_id ¦ expertise_id
-------------------------
23   ¦ 5
23   ¦ 6
25   ¦ 3
26   ¦ 5

table project_expertise (each project requires only 1 expertise):

project_id ¦ expertise_id
-------------------------
1   ¦ 4
2   ¦ 5
3   ¦ 3
4   ¦ 5

If so, and if the question is 'show me all students with expertise 5', things are really simple:
SELECT * FROM student_expertise WHERE expertise_id = 5

But I have a feeling I'm missing something in your question...

[dwilson]

To get things clear, is this what your tables look like?
table student_expertise (students can have more than 1 expertise):

student_id ¦ expertise_id
-------------------------
23 ¦ 5
23 ¦ 6
25 ¦ 3
26 ¦ 5

table project_expertise (each project requires only 1 expertise):

project_id ¦ expertise_id
-------------------------
1 ¦ 4
2 ¦ 5
3 ¦ 3
4 ¦ 5

Select student_id FROM Student_Expertise Inner Join
Project_Expertise On Student_Expertise.Expertise_ID = Project_Expertise.Expertise_ID
WHERE Project_ID = 2

That will return all student ID's having ANY of the required expertise. Considering that a project may require MULTIPLE field of expertise ... then supposing you are looking for students with ALL matching expertise ... a bit harder.

If that's the question, please clarify (possibly post your own data example) and I'll see what I can come up with.

[sun1967]

Sorry if I made things confusing. Both students and Projects can have more than one expertise. Like a Web project, which needs expertise in ASP, Database & Flash etc.

So the tables are like

table student_expertise (students can have more than 1 expertise):
student_id ¦ expertise_id
-------------------------
23 ¦ 4
23 ¦ 5
25 ¦ 3
26 ¦ 5

table project_expertise (each project can have more than 1 expertise):

project_id ¦ expertise_id
-------------------------
1 ¦ 4
1 ¦ 5
2 ¦ 3
2 ¦ 5

In this student 23 will be matched with project 1. But when you do query there will be more than one record that you have to check. That means first you have to extract expertise that is required for a project (That can be one, two or more), then see if there is a student with the same requirement.

I am really sick with this. But have worked out a way to go about finding a match by adding another column to studenttable as prefered_project_area and a column Project_area to the project table and then superviser can manually check from the displayed students their exact expertise to decide if they want to choose the student or not.

But I really want to know how the exact match can be found. I know it can be done.

Thanks mates. Your effort is much appreciated.
Sun67

[dwilson]

OK, here's the script I used to set up the scenario in MS SQL Server:
Use pubs
go
Create Table student_expertise (student_id int, expertise_id int)
go
Create Table project_expertise ( project_id int, expertise_id int)
GO

Insert Into student_expertise (student_id, expertise_id) values (23,4)
Insert Into student_expertise (student_id, expertise_id) values (23,5)
Insert Into student_expertise (student_id, expertise_id) values (25,3)
Insert Into student_expertise (student_id, expertise_id) values (26,5)
Insert Into student_expertise (student_id, expertise_id) values (26,3)

Insert Into project_expertise (project_id, expertise_id) values (1,4)
Insert Into project_expertise (project_id, expertise_id) values (1,5)
Insert Into project_expertise (project_id, expertise_id) values (2,3)
Insert Into project_expertise (project_id, expertise_id) values (2,5)

Now here's the query. Of course it's written for MS SQL Server, but I think it's standard SQL that any robust DB engine should handle. Of course you would put in a variable where I have hard-coded "1" as the project_id.

Select SE.student_id from
(Select student_id, count(student_id) as num_expertise From student_expertise inner join
project_expertise on student_expertise.expertise_id = project_expertise.expertise_id
Where project_id = 1 group by student_id) SE
Inner Join
(Select count(expertise_id) num_expertise from project_expertise where project_id = 1) PE On
SE.num_expertise >= PE.num_expertise

I have not tried this on a wider variety of scenarios, but it should work. If you find one in which it does not, please post as I'd like to see what I did wrong.

[sun1967]

Thanks a lot dwilson, it is working. You saved me from pulling my few leftover hair! I am using MS SQL server as well.

Thanks again.
Sun67