Comparing two tables, finding matches, with forign keys

[imstillatwork]

Tables:

members
-----------------------------------
id (unique) | member_type (o or c)
-----------------------------------

project_types
------------
id (unique)
------------

member2type
-----------------------------
member_id | projecy_type_id
-----------------------------

project
------------------------
id (unique) | county_id
------------------------

project2type
-------------------------------
project_id | projecy_type_id
-------------------------------

contractor2county
------------------------
member_id | county_id
------------------------


Query so far

	SELECT 
		p.id AS project_id
		,p.county_id AS project_county
		,p.project_type_id AS project_type
		,c2type.member_id AS contractor_id
		,c2type.project_type_id AS contractor_project_type
		,c2c.county_id AS contractor_county
	FROM project AS p
	LEFT JOIN project2type AS p2t ON p2t.project_id = p.id
	LEFT JOIN member2type AS c2type ON c2type.project_type_id = p.project_type_id
	LEFT JOIN members AS c ON c.id = c2type.member_id AND c.member_type = 'c'
	LEFT JOIN contractor2county AS c2c ON c2c.member_id = c2type.member_id
		
	WHERE c2c.county_id = p.county_id

This query will return results, but I need it onyl return rows where the contractor2type.project_type_id matches ALL of the project2type.project_type_ids

So a contractor selects multiple job types (concrete, flooring, etc...) that they are willing to work on. And a project owner selectes multiple project types that are required for the job.

right now it returns rows where any one of the contractors project_types matches any one of the projects project_types

So if a job requires 3 project_types, and a contractor has one of them, it matches, but the contractor should have to have all of them before that row is returned....

??? I thought it could be done in s sigle query, if not, I will find a long way to do it....

THANKS!






Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[r937]

cannot see county table

cannot see contractor table

cannot see project owner table

what is driving the query? the project? the member? usually there's a WHERE clause which restricts the output to a specific thing you're looking for, not all of them

e.g. contractors who qualify for a given project

the solution involves selecting the project types that each contractor has which are among the project types required for the project, then use GROUP BY and HAVING to see if the count equals the total count of project types required

single query

r937.com | rudy.ca

 

[imstillatwork]

county table only has a name and id, so that table is not joined, I use the county_id from the project table, and the countyId from the contractors2county table.

There is only a members table for contractors and project owners joined with member_type = 'c'

The project is the focus, the member I though could be joined in, with the proper constriants to match the project.

as it is, it matches counties fine, becuase the project county_id only hase to match one of many of the contractor2county county_ids

but the projects project_types need to all be matched agains a contractor that has at least all of the same project types.

I'll give it a few more tries, but running out of time.

thanks!




Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[imstillatwork]

	SELECT
		p.id AS project_id
		,m2t.member_id
		,c2c.county_id
		,p.county_id
	FROM project AS p
	LEFT JOIN project2type AS p2t ON p2t.project_id = p.id
	LEFT JOIN member2type AS m2t ON m2t.project_type_id = p2t.project_type_id
	
	LEFT JOIN contractor2county AS c2c ON c2c.member_id = m2t.member_id AND c2c.county_id = p.county_id

	GROUP BY p.id
	HAVING 
		COUNT(p.id) = COUNT(m2t.member_id)
		AND p.county_id = c2c.county_id

THANKS!!!!

This seemed to work well! Will there be any serious performance issues running this once per night, possibly on thousands of rows? I am storing the matches in a seperate table so this won't have to run all the time...



Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[imstillatwork]

nevermind...
Arg...

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[r937]

sorry i was unavailable for a while

you have the basic idea with the counts in the HAVING clause, except there should be no join condition in there

the idea is, you count the number of project types and see if that's as many as the project project types

i think ;-)

SELECT p.id           AS project_id
     , m2t.member_id
  FROM project AS p
LEFT OUTER
  JOIN project2type AS p2t 
    ON p2t.project_id = p.id
LEFT OUTER
  JOIN member2type AS m2t 
    ON m2t.project_type_id = p2t.project_type_id
LEFT OUTER
  JOIN contractor2county AS c2c 
    ON c2c.member_id = m2t.member_id
   AND c2c.county_id = p.county_id
GROUP
    BY p.id    
     , m2t.member_id
having count(distinct m2t.project_type_id)
     = count(distinct p2t.project_type_id)

r937.com | rudy.ca

 

[imstillatwork]

what you posted ran first as is, but on project as project_type_id of 1,3,5 and it is returning a row with a member with project_type_id of 1 and 8 - along with the correct member id that does match.



Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[imstillatwork]

what did I just write?

ok. it ran as i, but the results are still wrong.

there is a project with project_type_id's of 1,3,5
a row returns for a member with all project_type_id's selected
AND a member with project_type_id's of 1 and 8.

a member with 1 and 8 can not do a job that requires 1,3,and 5 so that row should not return.

does that make sense?

is there something more fundamental that is wrong?

should I play with sub-querying each row to get the project_types to match??


I gues for now I may have to just get the project info, and loop it out agains the member info? and get my results in cfml??




Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[r937]

depends on how much of a hurry you are in

if you catch me over the weekend (contact me via my site) and send me some test data, i'll have a look

r937.com | rudy.ca

 

[imstillatwork]

will do!

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!