• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

How to Find records related to different tables with out brining back duplicates

Hi There,
I am having trouble with a large query i am writing. This is the backgroud of whats happening.
I have a customer who has one product. there are 3 Tickets raised on this customers account,however 2 are for customer and 1 is for the product. The schema deals with this as follows:

Drop table CUSTOMER;
Drop table ACCOUNT;
Drop table TICKET;
DROP Table FIELDGROUP;

--CUSTOMER DETAILS
Create table CUSTOMER (
customer_number varchar(75)not null,
customer_id bigint not null);

ALTER TABLE CUSTOMER ADD CONSTRAINT customer_idPK Primary Key  (customer_id);
ALTER TABLE CUSTOMER ADD CONSTRAINT customer_idFK Foreign Key (customer_id) REFERENCES CUSTOMER (customer_id);

INSERT CUSTOMER (customer_number, customer_id) VALUES ('202903',202903);
INSERT INTO CUSTOMER  values('219492',219492);

--ACCOUNT DETAILS
Create table ACCOUNT (
Account_id int not null,
Customer_id int not null);

ALTER TABLE ACCOUNT ADD CONSTRAINT account_idPK Primary Key NONCLUSTERED (account_id);

INSERT INTO ACCOUNT VALUES (502927,202903)
INSERT INTO ACCOUNT VALUES (518627,219492)

--Fieldgroup Details
CREATE TABLE FIELDGROUP
( fieldgroupid bigint not null,
fieldgroupname varchar(50)not null);

ALTER TABLE FIELDGROUP ADD CONSTRAINT fieldgroupidPK PRIMARY KEY (fieldgroupid);

INSERT INTO FIELDGROUP VALUES (7,'Customer')
INSERT INTO FIELDGROUP VALUES (8,'ACCOUNT')


--TICKET DETAILS
Create table TICKET (
troubleticketid bigint not null,
ticketnumber  varchar(10) not null,
recordid bigint not null,
entereddate datetime not null,
fieldgroupid bigint not null);

ALTER TABLE TICKET ADD CONSTRAINT troubleticketidPK Primary Key NONCLUSTERED (troubleticketid);
ALTER TABLE TICKET ADD CONSTRAINT fieldgroupidFK Foreign Key (fieldgroupid) REFERENCES FIELDGROUP (fieldgroupid)


INSERT INTO TICKET VALUES (284760,'284760',202903,'2007-06-27 23:42:17.000',7)
INSERT INTO TICKET VALUES (290677,'290677',202903,'2007-07-18 09:55:53.000',7)
INSERT INTO TICKET VALUES (291855,'291855',502927,'2007-07-20 16:07:21.000',8)



--In my query i want to bring back details for both Customer entered (219492,202903)
--I want to see The ticket details for if the customer has a ticket and if they do not i still want the customers details to return
--The Ticket Table holds tickets for both Customer and Account, if there is a CustomerTicket the Fieldgroupid = 7 If there is an Account Ticket the fieldgroupid =8
--However the Schema for the DB links the following way
--If its a customerticket then customer.customer_id = ticket.recordid
--If its a account ticket then account.account_id = ticket.recordid
--Generally i would link Customer.customer_id = Account.customer_id

--The resutls i want to see is
Customer_Number   :Customer_id :Account_id  :TicketNumber  :Fieldgroup    :Recodrid
'202903'                   :202903         :502927        :502927             :8                      :502927      
'202903'                   :202903         :502927        :284760             :7                  :202903
'202903'                   :202903         :502927        :290677              :7                  :202903
'219492'                   :219492         :518627        :NULL                :NULL            :NULL


--I have tried muntiple things more then just below differnt sceraros but i can;t get the results
--i am looking for. here are some of the trials below:

--OPTION 1:
SELECT
c.customer_number,
C.customer_id,
A.account_id,
CASE WHEN F.fieldgroupid = 7 then acc_tt.ticketnumber WHEN f1.fieldgroupid = 8 then cust_tt.ticketnumber END TICKETNUMBER,
f.fieldgroupname,
CASE WHEN f.fieldgroupid = 7 then acc_tt.recordid When f1.fieldgroupid = 8 then cust_tt.recordid END RECORDID

FROM CUSTOMER c
LEFT JOIN ACCOUNT a on a.customer_id = c.customer_id
LEFT JOIN (SELECT *  FROM ticket t WHERE t.fieldgroupid = 8)acc_tt on acc_tt.recordid = a.account_id
LEFT JOIN (SELECT * FROM ticket t WHERE t.fieldgroupid = 7)cust_tt on cust_tt.recordid = c.customer_id
LEFT JOIN Fieldgroup f on f.fieldgroupid = acc_tt.fieldgroupid
LEFT JOIN fieldgroup f1 on f1.fieldgroupid = cust_tt.fieldgroupid



--OPTION 2:
SELECT  
c.customer_number,
a.customer_id,
a.account_id,
CASE WHEN Acc_tt.fieldgroupid = 8 then acc_tt.ticketnumber WHEN cust_tt.fieldgroupid = 7 then cust_tt.ticketnumber END TicketNumber,
CASE WHEN Acc_tt.fieldgroupid = 8 THEN acc_tt.fieldgroupid WHEN cust_tt.fieldgroupid = 7 then cust_tt.fieldgroupid END
from customer c
left join account a on a.customer_id = c.customer_id

Left join (select * from ticket t
                  --LEFT join account a on a.account_id = t.recordid
                  where t.fieldgroupid = 8)acc_tt on acc_tt.recordid = a.account_id
LEFT JOIN (select * from ticket t
                  --LEFT join customer c on c.customer_id = t.recordid
                        where t.fieldgroupid = 7) cust_tt on cust_tt.recordid = c.customer_id





--OPTION 3:
--I tried using the ticket info as a table in the select statement but i have about
--10 colunms from the real query to bring back allone from the Ticket table and i thought this
--was messy having to do a table for each field name?
SELECT c.customer_number,
c.customer_id,
a.account_id,
(SELECT TOP 1 t.ticketnumber  FROM ticket t WHERE t.recordid = a.account_id and t.fieldgroupid = 8)acc_tt,
(SELECT TOP 1 t.ticketnumber FROM ticket t WHERE t.recordid = c.customer_id and t.fieldgroupid = 7)cust_tt
FROM customer c
LEFT JOIN account a on a.customer_id = c.customer_id

This is the last resort on this i have spent a good 2 days workin on this alone! i have tried to recreate the tables etc as best as possiable. Your help is greatly needed on this.

Kind Regards,
Putoch


0
Putoch
Asked:
Putoch
  • 3
1 Solution
 
MinnaCommented:
Use a union instead --

SELECT
c.customer_number,
c.customer_id ,
a.Account_id ,
t.ticketnumber AS ticketnumber,
t.fieldgroupid,
t.recordid

FROM
CUSTOMER c
INNER JOIN ACCOUNT a on a.Customer_id = c.customer_id
LEFT JOIN TICKET t on c.customer_id = t.recordid
AND t.fieldgroupid = 7
UNION

SELECT
c.customer_number,
c.customer_id ,
a.Account_id ,
t.recordid as ticketnumber,
t.fieldgroupid
t.recordid
FROM
CUSTOMER c
INNER JOIN ACCOUNT a on a.Customer_id = c.customer_id
LEFT JOIN TICKET t on t.recordid = a.Account_id
AND t.fieldgroupid = 8
0
 
PutochAuthor Commented:
Hi,
Thank you for that, This works with my example however in my own database it will not work?
I had original used Unions but it as returning duplicat information. What i did was
use Customer /Account and then i put the ticket information all into one table so that the query would not only return accounts with ticket info but thoses with none also

left join (SELECT recordid,tt.status_id,tt.fieldgroupid,tt.work_group_id,tt.troubleticketid,tt.enteredby,
             tt.resolutiondescription,tt.problemdescription,tt.ticketnumber,tt.entereddate
             FROM TICKET tt
            INNER JOIN  ticket tt1 on tt1.troubleticketid = tt.troubleticketid
            WHERE tt1.entereddate > getdate()-60
AND tt.fieldgroupid = 7
                  ) subq on subq.recordid = c.customer_id
0
 
PutochAuthor Commented:
Sorry i had not finished my comment above completly
Hi,
Thank you for that, This works with my example however in my own database it will not work?
I had original used Unions but it as returning duplicat information. What i did was
use Customer /Account and then i put the ticket information all into one table so that the query would not only return accounts with ticket info but thoses with none also
--Customer Ticket
left join (SELECT recordid,tt.status_id,tt.fieldgroupid,tt.work_group_id,tt.troubleticketid,tt.enteredby,
             tt.resolutiondescription,tt.problemdescription,tt.ticketnumber,tt.entereddate
             FROM TICKET tt
            INNER JOIN  ticket tt1 on tt1.troubleticketid = tt.troubleticketid
            WHERE tt1.entereddate > getdate()-60
AND tt.fieldgroupid = 7
                  ) subq on subq.recordid = c.customer_id

And the same for Account ticket
left join (SELECT recordid,tt.status_id,tt.fieldgroupid,tt.work_group_id,tt.troubleticketid,tt.enteredby,
             tt.resolutiondescription,tt.problemdescription,tt.ticketnumber,tt.entereddate
             FROM TICKET tt
            INNER JOIN  ticket tt1 on tt1.troubleticketid = tt.troubleticketid
            WHERE tt1.entereddate > getdate()-60
AND tt.fieldgroupid =8
                  ) subq on subq.recordid = a.account_id


-----------
I also tried using the UNION and putting the ticket criteria (ticket >getdate()-60 days and fieldgroupid) in to the WHERE clause of each statement.
I know i didn't mention creiteria about date before but ididn't think it was necessary.

I know this is complecated as your query worked with the example i gave and i can not see what the difference is in the tables i am using as to why it won't work in mine, i'm kinda of asking you to work in the dark here i know, but any advise would be greatly appriciated.

Kind regards,
Putoch
0
 
PutochAuthor Commented:
I am going to close this question as it is unfair to ask you querys when you can't see the db and i cant re-crete it for you to help me.
Thank you on your answer above it works perfect on the example and i will use then again

Best wishes,
Putoch
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now