CODEWITHSUNDEEP
sqlsql-server
kooker
kooker

Reputation: 373

Set column value if certain record exists in same table

How could I create a view for a table with subset of the table's records, all of the table's columns, plus additional "flag" column whose value is set to 'X' if the table contains certain type of record? For example, consider the following relations table Relations, where values for types stand for H'-human, 'D'-dog:

 id | type | relation | related
--------------------------------
 H1 |  H   | knows    | D2 
 H1 |  H   | owns     | D2 
 H2 |  H   | knows    | D1 
 H2 |  H   | owns     | D1
 H3 |  H   | knows    | D1
 H3 |  H   | knows    | D2
 H3 |  H   | treats   | D1
 H3 |  H   | treats   | D2
 D1 |  D   | bites    | H3
 D2 |  D   | bites    | H3

There may not be any particular order of records in this table.

I seek to create a view Humans which will contain all human-to-dog knows relations from Relations and all of the Relations's columns and additional column isOwner storing 'X' if a human in a given relation owns someone:

 id | type | relation | related | isOwner
------------------------------------------
 H1 |  H   | knows    |    D2   |    X
 H2 |  H   | knows    |    D1   |    X
 H3 |  H   | knows    |    D1   |

but struggling quite a bit with this. Do you know of a way to do it, preferably in one CREATE VIEW call, or any way really?

Upvotes: 2

Views: 9135

Answers (3)

Andriy M
Andriy M

Reputation: 77737

You could also use PIVOT to achieve the desired result. I'll explain the method in details because the final query may appear confusing.

First, derive a subset from Relation where type is H ans relation either knows or owns, replacing the owns value with X:

SELECT
  id,
  type,
  relation = CASE relation WHEN 'owns' THEN 'X' ELSE relation END,
  related
FROM Relations
WHERE type = 'H'
  AND relation IN ('knows', 'owns')

Based on your example, you'll get this:

id  type  relation  related
--  ----  --------  -------
H1   H    knows     D2
H1   H    owns      D2
H2   H    knows     D1
H2   H    owns      D1
H3   H    knows     D1
H3   H    knows     D2

Next, apply this PIVOT clause to the result of the first query:

PIVOT (
  MAX(relation) FOR relation IN (knows, X)
) AS p

It will group rows with identical id, type, related values into a single row and split relation into two columns, knows and X:

id  type  related  knows  X
--  ----  -------  -----  ----
H1   H    D2       knows  X
H2   H    D1       knows  X
H3   H    D1       knows  NULL
H3   H    D2       knows  NULL

At this point you only need to rearrange the column set slightly for the output in the main SELECT clause, renaming knows to relation and X to isOwner along the way:

SELECT
  id,
  type,
  relation = knows,
  related,
  isOwner = X
...

Output:

id  type  relation  related  isOwner
--  ----  --------  -------  -------
H1   H    knows     D2       X
H2   H    knows     D1       X
H3   H    knows     D1       NULL
H3   H    knows     D2       NULL

NULLs, of course, can easily be substituted with empty strings, if that is necessary.

One final touch may be to add this additional filter to the main query:

WHERE knows IS NOT NULL

just in case there can be people that own dogs without actually knowing them (and you don't want those in the output).

So, the complete query would look like this:

SELECT
  id,
  type,
  relation = knows,
  related,
  isOwner = X
FROM (
  SELECT
    id,
    type,
    relation = CASE relation WHEN 'owns' THEN 'X' ELSE relation END,
    related
  FROM Relations
  WHERE type = 'H'
    AND relation IN ('knows', 'owns')
) AS s
PIVOT (
  MAX(relation) FOR relation IN (knows, X)
) AS p
WHERE knows IS NOT NULL
;

A SQL Fiddle demo for this solution is available here.

Upvotes: 1

plalx
plalx

Reputation: 43728

CREATE VIEW vHumanDogRelations
AS
SELECT
    id,
    type,
    relation,
    related,
    -- Consider using a bit 0/1 instead
    CASE 
        WHEN EXISTS (
            SELECT 1
            FROM Relations
            WHERE 
                id = r.id
                AND related = r.related -- Owns someone or this related only?
                AND relation = 'owns'    
        ) THEN 'X'
        ELSE ''
    END AS isOwner
FROM Relations r
WHERE 
    relation = 'knows'
    AND type = 'H'
    AND related = 'D';

Upvotes: 3

iruvar
iruvar

Reputation: 23394

You should be able to put the following select into the view definition

select *, case when exists 
(select * from Relations where id = r.id and relation= 'owns') then 'X'
else '' end as isOwner 
from Relations r

Upvotes: 2

Related Questions