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!
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.
Comments