advanced lists

14 posts by 2 authors in: Forums > CMS Builder
Last Post: March 27, 2015   (RSS)

By rez - March 25, 2015 - edited: March 25, 2015

It was painful, but I got it. How you can select before you join, or how it's known to look ahead for a join while not understanding yet what I'm selecting, I don't know. I would think the join should have been first.

SELECT <?php echo $TABLE_PREFIX ?>locations.num, CONCAT (<?php echo $TABLE_PREFIX ?>state.name, ' - ', city, ' - ',address)
  FROM `<?php echo $TABLE_PREFIX ?>locations`
  JOIN <?php echo $TABLE_PREFIX ?>state ON <?php echo $TABLE_PREFIX ?>locations.state = <?php echo $TABLE_PREFIX ?>state.num

By rconring - March 26, 2015

Glad to see you got the join working.  I have always used the state abbreviation in the state validation table as a key field.  Then, instead of storing the state record number in the locations file, you could store the 2 character state abbreviation.  That would make the state available without the join to the state table. 

SELECT num, CONCAT(city. ', ',abbreviation, ' - ', address)
  FROM `<?php echo $TABLE_PREFIX ?>locations`
  ORDER BY 'abbreviation, city'

Just a thought.

Ron Conring
Conring Automation Services
----------------------------------------
Software for Business and Industry Since 1987

By rez - March 26, 2015

Oh, that is convenient. However, in CMSB, the keys are always automatically num, right? How are you getting the abbreviation into the locations table as a key somewhere before this (if i am understanding correctly)?