sql ordering query- does this make sense? 1

[blondrob]

Didn't get a response to my last thread so i thought i'd try a simplified version.

Basically what i'm trying to do is order some results based on whether they match some criteria.

The query is of the form

SELECT distinct *, (apple='delicious' AND pear='rotten') as Relevance FROM tblfruit WHERE apple='delicious' OR pear='rotten' ORDER BY Relevance

so if apple='delicious' AND pear='rotten' Relevance would be 1

a typical result set could be

APPLE PEAR Relevance

delicious rotten 1
delicious rotten 1
delicious delicious 0
rotten rotten 0
rotten delicious 0

does this line of reasoning make sense? if not why not?

Help would be appreciated. Cheers,
Rob

 

[tlbroadbent]

Use a CASE statement. You'll want to change the where clause if you want the result you posted. I give one example. You may have a different idea in mind.

SELECT
distinct *,
Case
When apple='delicious' AND pear='rotten'
Then 1 Else 0 End As Relevance
FROM tblfruit
WHERE apple In ('delicious', 'rotten')
AND pear In ('delicious', 'rotten')
ORDER BY Relevance Desc

BTW: With the Distinct clause, the result set will not have duplicates.
[tt]
delicious rotten 1
delicious delicious 0
rotten rotten 0
rotten delicious 0[/tt] Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[blondrob]

Thanks that works, however it hasn't solved my problem.
It did let me figure out what's causing it. I'm using two tables. One holds all the main info, the other is a relational table to hold multiple location info about a person.

i.e.

tblperson:

person_id
name
age
sex
dob

tblperson_location:

person_id
location_id

when i have a case requiring me to look at both tables a duplication occurs if the case is satisfied.

Case when tblperson.person_id=tblperson_location.person_id AND tblperson_location.location_id=5 Then 1 Else 0 End as Relevance

if the above is satisfied i get the same result twice. The only difference is Relevance is 1 in one result and 0 in the other.

Help would still be appreciated.

 

[tlbroadbent]

1) You should use a JOIN query because of the relationship between the tables.
2) If there is a one-to-many relationship, the query will return multiple rows unless you tell it not to do that. You have nothing in the query to prevent multiple rows per person. All rows in the person_location table will be returned with the matching row of the person table. Of course, when the location = 5 the CASE statement returns 1. For all other locations, it returns 0.
3) Actually, you are not getting the same result twice. You are just not displaying all the columns that make the rows unique. What do you really want to see in the result. I'm very unclear on that. If you only want location 5, then you don't need a relevance sort. You need a where clause. I may be way off base here because I lack understanding. Some sample data and a look at your query would be helpful.

Here is an example of the JOIN query.

Select
p.person_id, p.name,
p.age, p.sex, p.dob,
l.location_id,
Relevance =
Case When l.location_id=5
Then 1 Else 0 End
From tblperson p
Join tblperson_location l
On p.person_id=l.person_id

In the next query, I added a where clause. This is just an example.

Select
p.person_id, p.name,
p.age, p.sex, p.dob,
l.location_id
From tblperson p
Join tblperson_location l
On p.person_id=l.person_id
Where l.location_id=5
Order by p.name
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[blondrob]

Thanks again, i've sorted the problem now (fingers crossed). I had a lot of conditions after the WHERE clause. The user can decide which is most important to them.

if you want to see what the query is for, it's used on this page:

http://lupton.bpweb.net/thelea.com/phppages/jobindex.php4