sql eliminating unwanted rows

Question

so I have made a select query like this:

select distinct
    f.[film name]
    ,f.city
    ,case rt.[type name]
    when 'director' then p.[participant name] else null end 'partecipant name'
    ,case rt.[type name]
    when 'director' then 'director' else null end 'role type'
from Films f
    inner join FilmParticipants fp on fp.filmID=f.filmID
    inner join Participants p on p.participantID=fp.participantID
    inner join RoleType rt on rt.roleID=fp.roleID
where f.city in(
    select f.city
    from Films f
    group by f.city
    having COUNT(*)>1
)
order by f.city

it yields this table:

film name | city   | participant name | role type
__________________________________________________
Dragon    | London | null             | null
Dragon    | London | Morty            | director
Dragon    | London | James            | director
Shrek     | London | null             | null

now I want to eliminate the first row, since I dont need it, it has nulls and I have the film name in the second row. but I cant do 'where [participant name]=null...etc..' because it will also remove my third row, which I need since his name only shows once. what do I do? and I cant use the 'max' function because it will eliminate the third row also.

logic explanation: the cases made sure that whenever it is not a 'director' role it is null role, and so is the participant name column(if role column is not a 'director' it is null). as to what to show if there is only one row of that film name, I need it to be seen, even if it has nulls. but if there is more than one row of that film it should eliminate the null rows of it and show all of those that are not nulls

it is a solution for that specific case, if there was another director for the 'Dragon' film it will only show one of them... I need a general solution that does something like 'if 'film name already exist and 'participant name'=null and 'role type'=null ONLY THEN you shouldnt show the row' — Eden, Jun 22 '17 at 20:23
If you have extra conditions (as it appears you do, since you're leaving the same comment on every answer that is posted), then [edit] your question to include those other conditions. People are answering the question you've asked here (correctly), and you're wasting their time if you've forgotten to include some details. — Ken White, Jun 22 '17 at 20:29
You need to provide some data that is actually representative of your data and what you expect for output. Do some rows have missing participants but do have a role? The opposite? There are a number of ways this could be interpreted and in the interest of not spending more effort chasing unknowns I would like to know. — Sean Lange, Jun 22 '17 at 20:44
the cases made sure that whenever it is not a 'director' role it is null role, and so is the participant name column(if role column is not a 'director' it is null). as to what to show if there is only one row of that film name, I need it to be seen, even if it has nulls. but if there is more than one row of that film it should eliminate the null rows of it. — Eden, Jun 22 '17 at 20:48
What would be the most helpful would be to share the table structure for your tables and sample data. As posted I don't see how you are getting NULL for these other columns because everything is an inner join. Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Jun 22 '17 at 20:51
See my updated answer to see if that is along the line of what you are looking for. — Sean Lange, Jun 22 '17 at 21:12

Aaron Dietz · Answer 1 · 2017-06-22T20:11:46.387

You need MAX() to complete the conditional aggregation you're doing. Try this:

SELECT f.[film name],
       f.city,
       MAX(CASE rt.[type name] 
           WHEN 'director' 
           THEN p.[participant name] 
           ELSE null 
           END) 'partecipant name',
       MAX(CASE rt.[type name]
           WHEN 'director' 
           THEN 'director' 
           ELSE null
           END) 'role type'
FROM Films f 
JOIN FilmParticipants fp ON fp.filmID=f.filmID 
JOIN Participants p ON p.participantID=fp.participantID 
JOIN RoleType rt ON rt.roleID=fp.roleID
WHERE f.city IN(
     SELECT f.city
     FROM Films f
     GROUP BY f.city
     HAVING COUNT(*)>1)
ORDER BY f.city
GROUP BY f.[Film Name], F.City

it is a solution for that specific case, if there was another director for the 'Dragon' film it will only show one of them... I need a general solution that does something like 'if 'film name already exist and 'participant name'=null and 'role type'=null ONLY THEN you shouldnt show the row' — Eden, Jun 22 '17 at 20:22

Sean Lange · Answer 2 · 2017-06-22T21:10:02.857

You can use some aggregation here. I formatted this quite a bit too so it isn't just a wall of text.

select MAX(f.[film name])
    , city
    , MAX(case rt.[type name] when 'director' then p.[participant name] else null END) as 'partecipant name'
    , MAX(case rt.[type name] when 'director' then 'director' else NULL END) as 'role type'
from Films f 
join FilmParticipants fp on fp.filmID = f.filmID 
join Participants p on p.participantID = fp.participantID 
join RoleType rt on rt.roleID = fp.roleID
where f.city IN
(
    SELECT f.city
    FROM Films f
    GROUP by f.city
    HAVING COUNT(*) > 1
)
GROUP BY f.city
ORDER by f.city

Given the new requirements and a lack of tables to work with I took your table that comes from your current query and put this together.

DECLARE @Films TABLE
(
    FilmName VARCHAR(20)
    , City VARCHAR(20)
    , ParticipantName VARCHAR(20)
    , RoleType VARCHAR(20)
)

INSERT @Films
(
    FilmName,
    City,
    ParticipantName,
    RoleType
)
VALUES
('Dragon', 'London', NULL, NULL),
('Dragon', 'London', 'Morty', 'director'),
('Dragon', 'London', 'James', 'director'),
('Shrek', 'London', null, null)
;

WITH SortedResults AS
(
    SELECT *
        , ROW_NUMBER()OVER(PARTITION BY FilmName ORDER BY ParticipantName) AS RowNum
    FROM @Films
)

SELECT sr.FilmName
    , sr.City   
    , sr.ParticipantName
    , sr.RoleType
FROM SortedResults sr
WHERE sr.RowNum > 1
OR FilmName IN
(
    SELECT f.FilmName
    FROM @Films f
    group by FilmName
    having MAX(ParticipantName) is null

Thanks for the edit Aaron but I moved the GROUP BY before the ORDER BY. I didn't see that you posted nearly an identical answer just before mine. — Sean Lange, Jun 22 '17 at 20:17
it is a solution for that specific case, if there was another director for the 'Dragon' film it will only show one of them... I need a general solution that does something like 'if 'film name already exist and 'participant name'=null and 'role type'=null ONLY THEN you shouldnt show the row' — Eden, Jun 22 '17 at 20:22
@Eden: Stop posting the same comment to every answer. If you need to add details to clarify the question, then edit your question and do so and stop wasting the time of people who are answering the question as you asked it. — Ken White, Jun 22 '17 at 20:28
@SeanLange Whoops, I was moving quickly and laughing about formatting woes. — Aaron Dietz, Jun 22 '17 at 20:30

sql eliminating unwanted rows

2 Answers2