CODEWITHSUNDEEP
sql-server
Adrian
Adrian

Reputation: 346

MS SQL - subquery to check if value exists in group by

Is there a way to build such a query ?

Table one has 10 values, table two has 100 values.

Table one:

id   | name   | value | weekStart  | weekEnd     | superValue
-----+--------+-------+------------+-------------+-----------
1    | tom    |  1    | 2016-01-01 | 2016-01-07  |    A
2    | tom    |  1    | 2016-08-01 | 2016-01-14  |    E
3    | mark   |  0    | 2016-01-01 | 2016-01-07  |    A

Table two:

id   | name   | age
-----+--------+----
1    | tom    | 16
2    | Xavier | 19
3    | mark   | 20
4    | bob    | 20
5    | alan   | 35

I have joined them (one record to many) based on their name, and then grouped them by name.

So that we have a resault something like this : 

id   | name   | value | (min)weekStart | (max)weekEnd 
-----+--------+-------+----------------+-------------
1    | tom    |  1    | 2016-01-01     | 2016-01-14
2    | mark   |  0    | 2016-01-01     | 2016-01-07

Is there a way to display an additional column with the value based on the first tables column (superValue) if any of the values for this name has value 'E' then 'Yes' else 'No'

id   | name   | value | (min)weekStart | (max)weekEnd | is it super ?
-----+--------+-------+----------------+--------------+---------------
1    | tom    |  1    | 2016-01-01     | 2016-01-14   |   Yes
2    | mark   |  0    | 2016-01-01     | 2016-01-07   |   No

Upvotes: 0

Views: 868

Answers (2)

Crouching Kitten
Crouching Kitten

Reputation: 1185

I didn't get, how you handled one.value, but this is my solution: (so you actually don't need a subquery for this.)

select
    `two`.`id`,
    `two`.`name`,
    max(`one`.`value`) as `value`,
    min(`one`.`weekStart`) as `minWeekStart`,
    max(`one`.`weekEnd`) as `maxWeekEnd`,
    sum(if(`one`.`superValue` = 'E', 1, 0)) > 0 as `is it super?`
from
    `two`
    inner join `one`
        on (`two`.`name` = `one`.`name`)
group by
    `two`.`name`
order by
    `two`.`id`;

It would be nicer if you reference table two by the id, and not by the name.

EDIT: A little explanation how it works:

sum(if(`one`.`superValue` = 'E', 1, 0)) > 0 as `is it super?`

The "if" statement inside the sum returns 1 is the value is 'E' and returns 0 otherwise. The sum just adds these 0's and 1's together, and at the end we check if the sum is larger than 0, meaning: Was there at least one 'E' value.

Upvotes: 1

Akshey Bhat
Akshey Bhat

Reputation: 8545

Yes it is possible. Just add another column to your select using case. Replace table-one with alias of table-one if given in inner join or actual name of table one.

select <existing-columns>,Case when Max(table-one.superValue) = 'E' then 'yes' else 'no' end as [is-it-super]
from <existing-joins>

Upvotes: 0

Related Questions