Ran across a problem today. I have a pretty complicated search that needed to
- Display results 0-30, 30-60, etc based on users input
- Sort based on any field the user chooses
- Has a one to many search criteria of an attribute that must be free text searched
I had most of this solved in a previous version but the new twist was the one to many relationship of the variable. Simple to fix by using the DISTINCT keyword, right? Wrong, looks something like "...WHERE id not in (SELECT DISTINCT TOP 30 id WHERE....ORDER BY name) ORDER BY name". Suddently the addition of the DISTINCT keyword requires that the field I order by be in the select clause, but in a NOT IN sub select clause I can only have one select field.
The solution I came with is to use the field I am sorting by for the select clause using PHP. The problem with that approach is that one field is not DISTINCT enough to get me the results I need. To overcome this I appended all of the possible fields together with the field that it was to be sorted by first. I then changed my order clause to order by whatever the first select cause happened to be.
The end result with possible where clauses stripped out looked like:
SELECT DISTINCT TOP 30 training_atc.id AS id, training_atc.name AS name, sources_atc.name AS source
FROM training_atc INNER JOIN
training_to_discipline ON training_atc.id = training_to_discipline.training_id INNER JOIN
webcal_categories ON training_to_discipline.discipline_id = webcal_categories.cat_id INNER JOIN
training_to_functionalArea ON training_atc.id = training_to_functionalArea.training_id INNER JOIN
functionalArea_atc ON training_to_functionalArea.functionalArea_id = functionalArea_atc.id INNER JOIN
sources_atc ON training_atc.source = sources_atc.id
WHERE (1 = 1) AND ((training_atc.name + sources_atc.name) NOT IN
(SELECT DISTINCT TOP 30 (training_atc.name + sources_atc.name)
FROM training_atc INNER JOIN
training_to_discipline ON training_atc.id = training_to_discipline.training_id INNER JOIN
webcal_categories ON training_to_discipline.discipline_id = webcal_categories.cat_id INNER JOIN
training_to_functionalArea ON training_atc.id = training_to_functionalArea.training_id INNER JOIN
functionalArea_atc ON training_to_functionalArea.functionalArea_id = functionalArea_atc.id INNER JOIN
sources_atc ON training_atc.source = sources_atc.id
WHERE 1 = 1
ORDER BY 1 DESC))
ORDER BY training_atc.name DESC
If only MS SQL had a LIMIT keyword like MySQL!!!
