join confusion

join confusion

• Quimbo

  It seems i can't a union for a few reasons.
  
  I have a specialty table that has fields. Specialty, and spec_id
  then I have 10 tables, one for each of the last ten years. these tables have fields. spec_id, ui, ia.
  
  So i need the tables field names to be changed and i (to incorporate the year in there name..like so:
  
  select x.specialty, x.sp_id, ;
  z.ui as "cur_docs ", ;
  a.ui as "2005_ui ", ;
  a.ui as "2005_ia ", ;
  b.ui as "2004_ui ", ;
  a.ui as "2004_ia ", ;
  c.ui as "2003_ui ", ;
  a.ui as "2003_ia ", ;
  a.ui as "2002_ui ", ;
  d.ui as "2002_ia ", ;
  e.ui as "2001_ui ",;
  a.ui as "2001_ia ", ;
  f.ui as "2000_ui ", ;
  a.ui as "2000_ia ", ;
  g.ui as "1999_ui ", ;
  g.ui as "1999_ia ", ;
  h.ui as "1998_ui ", ;
  g.ui as "1998_ia ", ;
  i.ui as "1997_ui ", ;
  g.ui as "1997_ia ", ;
  j.ui as "1996_ui ",;
  j.ui as "1996_ia ";
  from c:\spec_analysis\spec.dbf x; c:\spec_analysis\cur_spec.dbf z, c:\spec_add_2005.dbf a, c:\spec_add_2004.dbf b, c:\spec_add_2003.dbf c, c:\spec_add_2002.dbf d,;
  c:\spec_add_2001.dbf e, c:\spec_add_2000.dbf f, c:\spec_add_1999.dbf g,
  c:\spec_add_1998.dbf h, c:\spec_add_1997.dbf i, c:\spec_add_1996.dbf j;
  (This is where i need some sort of join that includes the every field from the first table (c:\spec_analysis\spec.dbf and then includes the same fields (if they exist) from the 10 other tables.)
  
  the problem is that not every year has all 100 specialties that i've listed in the first table (c:\spec_analysis\spec.dbf)
  
  
  Thanks in advance,
  Mike
  
  
  
  

• MunishGupta

  If you have the possibility of 100 different specialities and 10 different years, you can't put this data into a single table with one field for each combination of year and speciality. VFP tables are limited to 255 fields. The usual way to handle this kind of requirement is to turn it sideways. Have each record represent one specialty/year combination.
  
  What do you need to do with data
  
  Tamar
  
  

• Jan Kučera

  You don't need a join but a union IMHO.

  select specialty from table1 ;
  union ;
  select specialty from table2 ;
  .... ;
  into cursor Specialties

  With VFP versions prior to 9 you're limited to 10 joins (unions also count as join). You might need to break this in 2 SQLs (say union first up to 10 tables and union result with next up to 9 tables).

  
  

• SeanC

  You got me wrong. Union is not like that. I guess sp_id exists in all those yearly tables.

  select sp_id, "Current" as Year, ui from ("c:\spec_analysis\cur_spec") ;
  union all ;
  select sp_id, "2005" as Year, ui from ("c:\spec_add_2005") ;
  union all ;
  ... ;
  select sp_id, "1996" as Year, ui from ("c:\spec_add_1996") ;
  into cursor crsAllYears

  You can sum and create a crosstab from this one. Or you could sum separately (as you did) and create crosstab using arrays (IMHO arrays are best at it). Other years need not have all 100+ specialties. As I see you already have one master table with all the specialties to create initial crosstab. Here is a sample that I gave to Terry a while ago. Browse intermediate tables (representing his existing data before crosstab):

  * Create cursors
  * states tables might already be there
  * let's create if not
  lcSTATES = "AK,AL,AR,AZ,CA,CO,CT,DC,"+;
  "DE,FL,GA,HI,IA,ID,IL,IN,KS,KY,"+;
  "LA,MA,MD,ME,MI,MN,MO,MS,MT,NC,"+;
  "ND,NE,NH,NJ,NM,NV,NY,OH,OK,OR,"+;
  "PA,PR,RI,SC,SD,TN,TX,UT,VA,VT,WA,WI,WV,WY"
  Alines(aStates,m.lcStates,.T.,',')
  Dimension aStates[Alen(aStates),1]

  Create Cursor states (state c(2))
  Append From Array aStates

  Create Cursor dummy (trno i)
  For ix = 1 To 1000
  Insert Into dummy Values (m.ix)
  Endfor

  Select Top 30000 Padl(trNo,5,'0') As TransitNo,state As Region,Rand() ;
  from states,dummy ;
  order By 3 ;
  into Cursor MyRecs

  Create Cursor transits (transitNo c(5), Region c(2))
  Append From Dbf('MyRecs')
  *Select * from transits order by transitNo
  * Create cursors
  xTabTransits()
  Select xTabbed
  Browse

  Function xTabTransits && cross tab transits
  Local Array aStates[1]
  Select state,'C',3,0 From states Into Array aStates
  Local Array aStruc[Alen(aStates,1)+1,4],aDataRec[1,alen(aStates,1)+1]
  Acopy(aStates,aStruc,1,-1,5)
  aStruc[1,1] = "TransitNo"
  aStruc[1,2] = "C"
  aStruc[1,3] = 5
  aStruc[1,4] = 0
  aDataRec = ''
  Create Cursor xtabbed From Array aStruc
  Select transitNo,Region From transits Order By 1,2 Into Cursor crsTransits
  Scan
  If !(transitNo == aDataRec[1,1])
  aDataRec = 'No'
  aDataRec[1,1] = transitNo
  Scan While transitNo == aDataRec[1,1]
  aDataRec[1,Ascan(aStruc,region,1,-1,1,1+2+4+8)] = 'Yes'
  Endscan
  Insert Into xtabbed From Array aDataRec
  Skip -1
  Endif
  Endscan
  Endfunc
  

  If you look closely it's populating the rows in an array first and then inserting from that. In your case it's not much different and needs slight changes.

  
  
• join confusion