view results showing duplicates

I would collapse all those queries together as I can find no differences in the from clauses. Thsi will also place all the where conditions together which should be easier to maintain - as well as performing better overall.

SELECT
      a.Orderno AS [order No]
    , b.CaseType AS [Case Type]
    , CASE WHEN b.rate = - 1 THEN 'True' ELSE 'False' END AS [rate of Service]
    , a.Branch_No AS [Branch No]
    , a.Branch_phone AS [Branch Phone]
    , b.salesperson AS [SalesName Name]
    , b.crewid AS [crew id]
    , b.Tradingname AS [Trading Name]
    , c.Address1 AS [Address Line 1]
    , c.Address2 AS [Address Line 2]
    , c.Salutation AS [Salutation]
    , c.CustomerFirstname AS [Customer Firstname]
    , b.displayflag1 AS [Flag1]
    , b.displayflag2 AS [flag2]
    , a.order_id a.order_id AS order_id
    , b.position AS Rack
FROM
ORDER a
INNER JOIN sales b ON a.Order_ID = b.Order_ID
INNER JOIN Storelocation c ON a.store_ID = c.store_ID
INNER JOIN customer d ON a.order_ID = d.Order_ID
LEFT JOIN customer_Role e ON d.role_ID = e.role_ID
INNER JOIN customerDetails f1 ON d.detail_ID = f1.deail_ID
INNER JOIN CustomerDetails f ON a.detail_ID = f.detail_ID
WHERE ( b.CaseType IN ('Metals', 'Black steals', 'Sundry', 'TBA', 'Variation Order')
        AND (
            b.orderDate >= dbo.basedate(getdate())
            OR b.deliverdateDate >= dbo.basedate(getdate())
            )
        ) 
OR /* #2 */
    ( 
     e.ROLE = 'my contact'
        AND (
            b.ServiceDate >= dbo.basedate(getdate())
            OR b.CommittalDate >= dbo.basedate(getdate())
            )
        AND (
            b.displayflag1 = 1
            AND displayflag2 = 1
            )
        OR (
            b.displayflag1 = 1
            AND displayflag2 = 0
            )
        AND b.CaseType IN ( 'Consignment' , 'Double Booking' , 'Superior Sale' )        
     ) 
OR /* #3 */
    (
     e.ROLE = 'my contact'
        AND (
            b.ServiceDate >= dbo.basedate(getdate())
            OR b.CommittalDate >= dbo.basedate(getdate())
            )
        AND (
            b.displayflag1 = 1
            AND displayflag2 = 1
            )
        OR (
            b.displayflag1 = 1
            AND displayfalg2 = 0
            )
        AND b.CaseType IN ( 'Consignment' , 'Double Booking' , 'Superior Sale' )        
    ) 
OR /* # 4 */
    (
     e.ROLE = 'my contact'
        AND (
            b.ServiceDate >= dbo.basedate(getdate())
            OR b.CommittalDate >= dbo.basedate(getdate())
            )
        AND (
            b.displayflag1 = 0
            AND displayflag2 = 1
            )
        AND b.CaseType IN ( 'Consignment' , 'Double Booking' , 'Superior Sale' )        
    )

Select allOpen in new window

I have not tried to simplify further. Please indicate if this helps with this issue as it is - before trying for simplification.

ahh now the light has gone on I will try this.

but what about the fields I have to null out. that is why I needed the union when the case type is true I need to NULL out all the fields but it needs to be displayed.
When Displayflag2 is 1 only I need to null out the contact details.

only when you have this condition
displayflag1 =1 and displayflag2 =1 or displayflag1 = 1 and displayflag2 = 0
can I show everything... contact role is related to displayflag1.

sorry correction the rateflag is the hierarchy flag not the case type.

Is this for a report? What is the reporting tool?

anyway, try the combine query please

Appolgies its been a long day, here is the corrected view I cannot send the real data but thread is the same, the third union I have to null out the fields when display2 is selected as you don't want to see the customer contact details as well I have corrected the date fields.
union-view.txt

Do you understand the difference between UNION and UNION ALL?

You have 4 subqueries, each have identical FROM clauses.
There are differences in the SELECT clauses
and whilst there are 4 different WHERE clauses 3 of them are quite similar

However, due to the differences introduced to the rows with the selection lists - you will not achieve the desired "simplification" using UNION

just a tiny difference in rows is considered unique by UNION (or DISTINCT)

I believe you are trying to replicate or produce a "report" - SQL is not a report writer.

I cannot "picture" what you are trying to do exactly - what are your "expected results"
Q-28161257.xlsx

I will come back to  you on this I was asked to combine two views into one, I had disagreed as I knew I would come across this issue of duplication.
they are extract scripts from sql to be displayed on websites.
but I am using dummy data to on exchange to explain the problem.
I cannot use company data, but I will consult with a sql developer and see what they say.
thanks

you should not expect SQL to do this form of manipulation - it should be performed within the programmic logic layer - all you will end-up with is a quite a mess

truly - this is not a good idea in SQL

does the site use PHP? .Net? - it can (and should) be done there
- this manipulation of data for presentation is NOT an sql task