table manipulation

table manipulation

• earthmaker

  dave, you all over these threads....ur the man!
  
  thanks
  
  
  

• Erpman

  Full outer Join
  
  dave
  
  
  

• Michael Lam

  Here you go:
  
  SELECT A.CITY,SUM(A.PACNT) AS PACNT,SUM(A.PISCNT) AS PISCNT,SUM(A.APNCNT) AS APNCNT FROM ;
  (SELECT CITY,PACNT,000000000 AS PISCNT ,000000000 AS APNCNT FROM PA;
  UNION ALL;
  SELECT CITY,000000000 AS PACNT,PISCNT ,000000000 AS APNCNT FROM PIS ;
  UNION ALL;
  SELECT CITY,000000000 AS PACNT,000000000 AS PISCNT ,APNCNT FROM APN) A;
  GROUP BY A.CITY
  
  I told you complex unions are tricky...
  

  Dave
  

  
  
  
  

• Tony V123

  After further look at my data, i seem to be excluding some info.
  
  I have three tables that list cities and different data in those cities, some are shared on all three tables and some are not. I need all the possible cities from all three tables, and the count of what data is in that city froma certian table.
  
  i tried
  
  select a.city as city, a.pis ,b.APN, c.PA:
  from pisTemp1 a full outer join (npTemp1 b full outer join paTemp1 c on c.city = b.city);
  on b.city = a.city;
  group by a.city;
  into cursor final
  
  but this only takes all the cities possible from table pisTemp1. I could do a full outer join in all three orders of the tables, but then how would i join all the data into one table
  
  
  
  

• Brandt-Lassen

  More detail--> three tables, PA, PIS, APN.
  
  They all have cities listed with corresponding data. ie.
  PA
  city paCNT
  Appleton 1
  cowville 3
  tinbucktoo 2
  zilby 4
  
  PIS
  city pisCNT
  Appleton 3
  danstuck 4
  tinbucktoo 1
  zebra 2
  
  APN
  city apnCNT
  Appleton 6
  danstuck 8
  horseland 16
  zilby 8
  
  i need all possible cities on all three lists, and a corresponding data count per table. ie
  
  final
  city paCNT pisCNT apnCNT
  Appleton 1 3 6
  cowville 3 0 0
  danstuck 0 4 8
  horseland 0 0 16
  tinbucktoo 2 1 0
  zebra 0 0 0
  zilby 4 2 8
  
  Now, if i do a full outer join on one table, as i stated earlier
  
  (
  select a.city as city, iif(isnull(a.PIS),0,a.PIS) as PIS, iif(isnull(b.APN),0,b.APN) as APN, iif(isnull(c.PA),0,c.PA) as PA;
  from pisTemp1 a full outer join (npTemp1 b full outer join paTemp1 c on c.city = b.city);
  on b.city = a.city;
  group by a.city;
  into cursor finalPIS
  )
  
  it seems to leave out cities that aren't in the pis table. Is there a way to completely fill this table with every possible city and the corresponding count of pa's,pis's and apn's..placing a zero in the table of the city doesn't exist in the corresponding table
  
  Mike
  
  
  
  
  
  
  

• srtplus

  You definitely need to eliminate the GROUP BY in your query.
  
  I think the problem you're running into is that you're including only a.city in the results, so if a city isn't in table a, you have no way to get its name. I think to be sure you get the right results, you need to include a.city, b.city and c.city.
  
  Tamar
  
  

• GGardiner

  It’s nearly imposable to give you an exact answer without seeing your data or at least data structure…..
  

  But I think the best way to approach this is to use Union, l outer joins will give you separate fields for cities in pisTemp1 and npTemp1, I may be wrong but I don’t think that is what you are looking for,
  
  Select <> from pisTemp1 ;
  Union all;
  Select <> from npTemp1;
  Union all;
  Select <> paTemp1;
  into cursor final

  Then do your summarizing.
  

  Using Complex unions can be tricky especially if you lack experience with them,
  To make it more understandable (logically) you may want to create a table\cursor will all the fields you need then append into it all the data from the other 3 tables.

  Then do your summarizing.

  
  
  
• table manipulation