Query Question 2

[Uprightguy]

Is there a better way to do this Query?
I have a large database of invoices. These invoices are not grouped by the customer and all of them have 4 fields of phone numbers. The phone numbers for a particular customer are not always the same but they will always have at least 1 phone number in the invoice that will tie it to the other invoices. So even though an invoice only has 4 phone numbers the customer may have 10 or more phone numbers linked to his account.

The purpose of the query is to get all phone # that a customer has ever used in an invoice.

the query starts off with 1 invoice and pulls that invoice into a temp file that has the 4 phone numbers. it then queries the main table and gets all invoices that have those 4 phone numbers. I figured the best way to get all the numbers is to do this query in a loop continually until no further results.

SELECT Cust_phone,Sec_phone,Fax,Text_Num
into #N_Orders_tmp1
FROM <Database>.<DBO>.<Table> 
WHERE <Table>.orderID=1236666

Select Cust_phone,Sec_phone,Fax,Text_Num
    into #N_Orders_tmp2 
    FROM <Database>.<DBO>.<Table>
    WHERE <Table>.cust_phone in( select cust_phone from #N_Orders_tmp1) or  
	  <Table>.cust_phone in( select Sec_phone from #N_Orders_tmp1) or
	  <Table>.cust_phone in( select fax from #N_Orders_tmp1) or
	  <Table>.cust_phone in( select Text_num from #N_Orders_tmp1) or
	  <Table>.sec_phone in( select cust_phone from #N_Orders_tmp1) or  
	  <Table>.sec_phone in( select Sec_phone from #N_Orders_tmp1) or
	  <Table>.sec_phone in( select fax from #N_Orders_tmp1) or
	  <Table>.sec_phone in( select Text_num from #N_Orders_tmp1) or
	  <Table>.fax in( select cust_phone from #N_Orders_tmp1) or  
	  <Table>.fax in( select Sec_phone from #N_Orders_tmp1) or
	  <Table>.fax in( select fax from #N_Orders_tmp1) or
	  <Table>.fax in( select Text_num from #N_Orders_tmp1) or
	  <Table>.text_num in( select cust_phone from #N_Orders_tmp1) or  
	  <Table>.text_num in( select Sec_phone from #N_Orders_tmp1) or
	  <Table>.text_num in( select fax from #N_Orders_tmp1) or
	  <Table>.text_num in( select Text_num from #N_Orders_tmp1)

 

[TamarGranor]

I wonder if you could do this with a recursive CTE, something along these lines:
[pre]
WITH cte_phones
AS (

SELECT Cust_phone,Sec_phone,Fax,Text_Num
FROM <Database>.<DBO>.<Table>
WHERE <Table>.orderID=1236666
UNION
Select Cust_phone,Sec_phone,Fax,Text_Num
into #N_Orders_tmp2
FROM <Database>.<DBO>.<Table>
JOIN cte_phones
ON <Table>.cust_phone = cte_phone.cust_phone or
<Table>.cust_phone cte_phone.Sec_phone or
<Table>.cust_phone in cte_phone.fax or
<Table>.cust_phone in cte_phone.Text_num or
<Table>.sec_phone in cte_phone.cust_phone or
<Table>.sec_phone in cte_phone.Sec_phone or
<Table>.sec_phone in cte_phone.fax from or
<Table>.sec_phone in cte_phone.Text_num or
<Table>.fax in cte_phone.cust_phone or
<Table>.fax in cte_phone.Sec_phone or
<Table>.fax in cte_phone.fax from or
<Table>.fax in cte_phone.Text_num or
<Table>.text_num in cte_phone.cust_phone from or
<Table>.text_num in cte_phone.Sec_phone or
<Table>.text_num in cte_phone.fax or
<Table>.text_num in cte_phone.Text_num)

SELECT * FROM cte_phone;[/pre]

I have to add that this is a good example of why storing phone numbers in differently named fields is a bad idea. Much better to have a separate table and a one-to-many relationship.

Tamar

 

[Andrzejek]

Wouldn't be easier to create e View:

select cust_phone as ContactNumber from #N_Orders_tmp1
UNION
select Sec_phone from #N_Orders_tmp1
UNION
select fax from #N_Orders_tmp1
UNION
select Text_num from #N_Orders_tmp1

and query against this view?


---- Andy

There is a great need for a sarcasm font.

 

[fredericofonseca]

slightly different approach

SELECT orderid
     , phones.phone
into #N_Orders_tmp1
FROM <Database>.<DBO>.<Table> 
outer apply (select cust_phone as phone
             union
             select sec_phone
             union
             select fax
             union 
             select text_num

            ) phones
WHERE <Table>.orderID=1236666
and phones.phone is not null

Select Cust_phone,Sec_phone,Fax,Text_Num
    into #N_Orders_tmp2 
    FROM <Database>.<DBO>.<Table>
    outer apply (select cust_phone as phone
             union
             select sec_phone
             union
             select fax
             union 
             select text_num

            ) phones
    inner join #N_orders_tmp1  no1
    on no1.phone = phones.phone

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Uprightguy]

Thank you all for your answers in attempting each I have learned much and have adjusted the code to get the necessary records.

  Declare @pOrderid as int
  set @pOrderid = 1263636
  IF OBJECT_ID('tempdb..#N_Orders_tmp1') IS NOT NULL drop table #N_Orders_tmp1
  IF OBJECT_ID('tempdb..#N_Orders_tmp2') IS NOT NULL drop table #N_Orders_tmp2
  IF OBJECT_ID('tempdb..#N_Orders_tmp3') IS NOT NULL drop table #N_Orders_tmp3
   
--Select Original invoice phone numbers based on orderid passed 
  SELECT <Table>.orderid, phones.phone
    into #N_Orders_tmp1
    FROM <database>..<table> 
    outer apply (select cust_phone as phone
                 union
                 select sec_phone
                 union
                 select fax
                 union 
                 select text_num
                )phones
    WHERE <Table>.orderid=@pOrderid
          and phones.phone is not null and Phones.phone > 0

  --Select All invoices from invoices which have the 4 phone number in the first query
    Select distinct <Table>.orderno, Phones2.Phone as phone
      into #N_Orders_tmp2 
      FROM <Database>..<Tabel>
      outer apply (select cust_phone as phone
                   union
                   select sec_phone
                   union
                   select fax
                   union 
                   select text_num
                  ) phones2
      inner join #N_orders_tmp1 No2
          on No2.phone = phones2.phone

   -- Get all phone numbers from above query by orderID as the above query only pulled phone numbers 
   -- that matched the original invoice and did not add the new numbers that may have 
   -- daisy chained the invoices together
  SELECT <Table>.orderID, phones3.phone
    into #N_Orders_tmp3
    FROM <Database>..<Table> 
    outer apply (select cust_phone as phone
                 union
                 select sec_phone
                 union
                 select fax
                 union 
                 select text_num
                )phones3
    inner join #N_Orders_tmp2 no3 
	 on no3.orderID =<Table>.orderID
        and phones3.phone is not null and Phones3.phone > 0
  
 ----	select the fields we really want to pull from results and one more loop to get missing invoices 
Select distinct orderID,cust_phone,Sec_phone,Fax,Text_num 
      FROM <database>..<Table>
      outer apply (select cust_phone as phone
                 union
                 select sec_phone
                 union
                 select fax
                 union 
                 select text_num
                )phones4
      inner join #N_orders_tmp3 No4
          on No4.phone = phones4.phone
       order by orderid desc