Return one row in a view based on a left join column value

westeast

I have a view where I am replacing the foreign key values with their reference values through a series of left joins.

One of these tables, can have a many to one relationship where I'm joining

Example.exampleid = SLA.exampleId.

My issue is that the SLA table has a column where the SLa.type can be A or B and then the SLA.value will have a number. This creates a duplicate row in my view where the only difference is the SLA.type and SLA.value.

I want it to return these columns where the SLA.type is A and still not break the view when there is nothing to find in the table for a given example.exampleId

E.g. My view select query in a concise shortform:

Select Example.exampleId, SLA.type, SLA.value
FROM Example 
   LEFT JOIN SLA ON Example.exampleId = SLA.exampleId
WHERE SLA.type <> "B" OR SLA.type IS NULL or SLA.value IS NULL

An example.exampleId will only ever have two rows in the SLA table, one for type A and one for type B.

Any ideas will be appreciated!

Shadow

Move the SLA.type <> "B" condition into the join clause because it will apply to the right hand side column only in this case. In the weher clause the condition applies to the whole dataset:

Select Example.exampleId, SLA.type, SLA.value
FROM Example 
   LEFT JOIN SLA ON Example.exampleId = SLA.exampleId and SLA.type <> "B"

Although, I would consider using SLA.type = "A", just in case you will introduce a new SLA type.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

MySQL left join first and second results and return as one row

From Dev

Left Self Join return one row per 'primary'

From Dev

Left Self Join return one row per 'primary'

From Dev

Return value pairs based on first row and column

From Dev

MySQL LEFT JOIN only one row, ordered by column without subquery

From Dev

MySQL LEFT JOIN only one row, ordered by column without subquery

From Dev

LINQ left join with only the row having maximum value of a column

From Dev

Mysql Left join with One row

From Dev

SQL: Inner Join return one row based on criteria

From Dev

Move row entry up by one depending on value in column to the left

From Dev

Move row entry up by one depending on value in column to the left

From Dev

query for join two column and get one row per left with column that have array of right table refrenced to left table row

From Dev

Flag a row in one column based on past value in another column

From Dev

how to get only one record based on column value in oracle join

From Dev

Return row of Data Frame based on value in a column - R

From Dev

Return value in dataframe based on row index, column reference

From Dev

Return unique row when using GROUP BY based on a column value

From Dev

Return value in dataframe based on row index, column reference

From Dev

Return row of Data Frame based on value in a column. R script

From Dev

Return Only Last Row LEFT JOIN

From Dev

SQL - Return Row Even If LEFT JOIN Fails

From Dev

MySQL left join limit to one row

From Dev

MySQL left join limit to one row

From Dev

Left join two values in one row

From Dev

LEFT OUTER JOIN selects just one row

From Dev

SQL Add a Column using Left Join in a view

From Dev

Left join with count return one result

From Dev

How to find a row using one column and return the value of that same row in another column

From Dev

Select distinct on Left Join for only one column

Related Related

  1. 1

    MySQL left join first and second results and return as one row

  2. 2

    Left Self Join return one row per 'primary'

  3. 3

    Left Self Join return one row per 'primary'

  4. 4

    Return value pairs based on first row and column

  5. 5

    MySQL LEFT JOIN only one row, ordered by column without subquery

  6. 6

    MySQL LEFT JOIN only one row, ordered by column without subquery

  7. 7

    LINQ left join with only the row having maximum value of a column

  8. 8

    Mysql Left join with One row

  9. 9

    SQL: Inner Join return one row based on criteria

  10. 10

    Move row entry up by one depending on value in column to the left

  11. 11

    Move row entry up by one depending on value in column to the left

  12. 12

    query for join two column and get one row per left with column that have array of right table refrenced to left table row

  13. 13

    Flag a row in one column based on past value in another column

  14. 14

    how to get only one record based on column value in oracle join

  15. 15

    Return row of Data Frame based on value in a column - R

  16. 16

    Return value in dataframe based on row index, column reference

  17. 17

    Return unique row when using GROUP BY based on a column value

  18. 18

    Return value in dataframe based on row index, column reference

  19. 19

    Return row of Data Frame based on value in a column. R script

  20. 20

    Return Only Last Row LEFT JOIN

  21. 21

    SQL - Return Row Even If LEFT JOIN Fails

  22. 22

    MySQL left join limit to one row

  23. 23

    MySQL left join limit to one row

  24. 24

    Left join two values in one row

  25. 25

    LEFT OUTER JOIN selects just one row

  26. 26

    SQL Add a Column using Left Join in a view

  27. 27

    Left join with count return one result

  28. 28

    How to find a row using one column and return the value of that same row in another column

  29. 29

    Select distinct on Left Join for only one column

HotTag

Archive