I am fairly new to SQL and have run into a problem I can't figure
out. I am trying to construct a query that returns an occurrence
ranked list of results.
I have the following tables and fields:
components
--------------------
id
name
componentsToPartsList
-------------------------------------------
componentID
listID
partsLists
------
id
name
I want to get results ranked by the number of components listed in a
query that are in the same list. For example, list id 1 with the
name 'robot' has parts bolt (component id 1), pincer (component id
3), and wheel (component id 4). List id 2 with name 'nuts and bolts'
has bolt and nut (component id 2) only in it. If I pass in bolt,
pincer and wheel I'd like to get back first 'robot' and then 'nuts
and bolts.' If I pass in nut and bolt I'd like to get back 'nuts and
bolts' and then 'robot.' I have a query that will return the
relevant partsListIDs:
SELECT componentsToPartsList.listID
FROM componentsToPartsList
WHERE componentsToPartsList.componentID IN(
(SELECT components.id FROM components WHERE components.name =
"nut"),
(SELECT components.id FROM components WHERE components.name =
"bolt");
}
But in the case of this query there are duplicates in the result. I
would like to use these duplicates to rank (I assume using ORDER BY)
the results based on the number of duplicates. Can anyone recommend
a good method of doing this?
->Ben
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
