CODEWITHSUNDEEP
mysqlsqldatabasedatabase-schema
Damian Czapiewski
Damian Czapiewski

Reputation: 881

Advanced filter in SQL

I have a problem with filtering in my SQL. Please take a look at the schema of database.

enter image description here

Now, I'd like to get programmers who know PHP with skills ●●/●●● and Java with skills ●●●. I executed following query:

SELECT p.name, p.city FROM programmers p INNER JOIN skills s
ON p.id = s.programmer
WHERE ( s.lang = 'PHP' AND s.level > 1 ) OR ( s.lang = 'Java' AND s.level = 3 );

What I got is

But Christopher does not know Java, yet he is returned. Replacing OR with AND between main conditions cause nothing is returned.

Moreover, I want to get all skills of a programmer in one row; here Joshua is in two separate rows. How to fix it?

All things considered, last questions. How to get:

Hope it's possible. Thanks in advance.

EDIT Operations focused on dates are no problem and there are no need to calculate the difference between dates, SQL can contain just year, i.e. 2010. Generally date is not the point.

Upvotes: 0

Views: 2425

Answers (2)

TommCatt
TommCatt

Reputation: 5636

This will give you what you want, but it's not at all flexible.

select p.name, p.city, php.lang, php.level, j.lang, j.level
from   programmer p
join   skills     php
  on   php.programmer = p.id
join   skills     j
  on   j.programmer = p.id
where  php.lang = 'PHP' and php.level > 1
  and  j.lang = 'Java' and j.level = 3;

If you will be doing a lot of these kinds of queries, you might look into creating a view for each skill set:

create view JavaSkills as
select programmer, lang, level, exp_from
from   skills
where  lang = 'Java'

Then the query would be 

select p.name, p.city, php.lang, php.level, j.lang, j.level
from   programmer p
join   PhpSkills  php
  on   php.programmer = p.id
join   JavaSkills j
  on   j.programmer = p.id
where  php.level > 1
  and  j.level = 3;

The execution plan should come out to be similar if not identical but the query is a little easier to read. This is also rather awkward to maintain, but one does what one can. ;)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

This is an example of a "set-within-sets" problem. I like to solve these using aggregation and having:

SELECT p.name, p.city
FROM programmers p INNER JOIN
     skills s
     ON p.id = s.programmer
GROUP BY p.name, p.city
HAVING SUM( s.lang = 'PHP' AND s.level > 1 ) > 0 AND
       SUM( s.lang = 'Java' AND s.level = 3 ) > 0;

EDIT:

If you want the list of skills (which wasn't part of the original question), then use group_concat():

SELECT p.name, p.city, group_concat(s.lang, ':', s.level separator '; ');
FROM programmers p INNER JOIN
     skills s
     ON p.id = s.programmer
GROUP BY p.name, p.city
HAVING SUM( s.lang = 'PHP' AND s.level > 1 ) > 0 AND
       SUM( s.lang = 'Java' AND s.level = 3 ) > 0;

Upvotes: 2

Related Questions