Forums: Back End:

I've got a table of projects (named projects), each with a unique id: project_id

I've got a second table of matches (named projects_related) with two fields: primary_id and secondary_id.

projects_related is for matching two rows from the projects table together. What I'm trying to figure out though is if I need to create two entries for each match (in each direction) or if I can search for the result in either field.

So, for instance, if I want to link the project_id 6 and project_id 4 together, right now I'm creating two entries in projects_related:

6 | 4
4 | 6

Is there a way that I can just create one row:

4 | 6

And be able to determine that match no matter which project_id I start with to search?
Basically, can I query projects_related to find a match for an id in EITHER field, rather than a specific one?

Does this make sense? (I've probably don't a poor job of the explanation).

do you have an example of a query you use now? that would probably make things more clear.

I haven't actually created the query yet, I'm just thinking it through.

But basically, it seems like I'll have to know which field I'm querying, won't I?

So my query will basically be something like this:

SELECT P.* FROM projects P, projects_related PR
WHERE P.project_id = PR.secondary_id
AND PR.primary_id = '$foo'

SELECT P.* FROM projects P, projects_related PR
WHERE P.project_id = PR.primary_id
AND PR.secondary_id = '$foo'

Basically, I'm looking for the query that is:

"if either field in projects_related matches $foo, take the other field and use it's value as the project_id in projects"

Effectively your projects related table is a lookup table.

You could get away with a single quey though:

SELECT P.* FROM projects P, projects_related PR
WHERE (P.project_id = PR.primary_id AND PR.secondary_id = '$foo') 
OR (P.project_id = PR.secondary_id AND PR.primary_id = '$foo')

Tech - that's perfect. I totally forgot about OR.


Thanks!