how do i select to make a row and fill in the blank with the default

searain

Table: members

Name ID Skills

John 1 ASP
Steff 2 PHP
Steff 2 ASP
David 3 PHP
Bob 4 PERL

input search values, $inputid, $inputskills

SELECT * FROM members WHERE ID='$inputid' AND SKILL='$inputskills'

So if $inputid=1, $inputskills=PHP,

nothing!!!

So if $inputid=1, $inputskills=ASP

John 1 ASP

So if $inputid=2, $inputskills=PHP,

Steff 2 PHP

BUT, what i also want here is, say ASP is a default language.

if $inputid is matched, $inputskills is not matched, but this record has the ASP as the skills, which is the default one i pre-set, i still want that record in the result row.

So if $inputid=2, $inputskills=PHP,

Steff 2 PHP
(id matched, skills matched, so we don't have to bring in the other record, even id 2 also has the default skill ASP)

So if $inputid=1, $inputskills=PHP,

John 1 ASP

(because id matched, ,skill not match but this matched record also has the skills as the default one ASP.)

So if $inputid=4, $inputskills=PHP,

nothing

(because id matched, skill not match and matched record does not have the default skill, ASP)

How to write this SQL?

Lars_Berg

SELECT * FROM members 
WHERE ID='$inputid' 
AND (SKILL='$inputskills'  or SKILL = 'ASP')

Elena_Mitovska

Hope this query will help:

SELECT * FROM members WHERE id=$id AND IF(SKILLS='inputskills', SKILLS='inputskills', SKILLS='ASP') limit 1

So if I got yuor points, yuor task is like that:

At first query searches if the member has skills=$inputskills, if he doesn't have such skills, query searches whether member has defaul skill ASP among skills, if so query returns the record, if memebr has neither searched $inputskills not default ASP skill, query doesn't return any record.

Elena_Mitovska

Hope this query will help:

SELECT *, NULLIF(SKILLS,'$inputskills ') as row_order FROM skills WHERE ID=$inputid AND IF(SKILLS='$inputskills ', SKILLS='$inputskills ', SKILLS='ASP')
order by row_order desc limit 1

So if I got yuor points, yuor task is like that:

It was quite easy to choose those members that has skill $inputskills or default skill, main problem was in case of member that has BOTH $inputskills AND defaul skill to return only record with $inputskills (record with default skill didn't have to be retrieved). I made up only solution, I retrive all records for member : record with $inputskills and with default skill, then sort records the way as the record with $inputskills would be on top and then limit the recordset "limit 1". So only record with $inputskills in retrieved.
Hope it is not that confusing 🙂)
If there are any problems with query, please, let me know

searain

Thanks!

This SQL is a little too much for me and I am using Interbase/firebird. I think "limit" is not working there too.

I used another approach to solve the problem. i did it in 4 steps, and used php variables (array) to save the temoprary results of each step.

1) select the results, both id and skills matched.

2) select the results, id matched and but skills not matched.

3) in the step 2 results, select the skills matched the default skill (in this example, ASP).

4) combine step 1 and step 3.

this way, every step is a very basic sql query.

I think the trade off is the "performance", use the one SQL you guys give may be faster than the approach I use. Right? But my database is not big, and it perform these SQLs in less than a second even from web access. So I can live with that.

Or it dependends on the databases? I remember MySQL ( i don't use that one very often.) said sub select is not supported or it is too slow in MySQL.

Thanks! Guys! Could anyone also give me brief advice on why we have to write a very complicated SQL when it could be done in several steps simple SQL? It must be there for a reason. Performance? or sometimes that is the only way to do that?