create list field from merged field in two other tables

2 posts by 2 authors in: Forums > CMS Builder
Last Post: July 4, 2011 (RSS)

Archived
Reply
Reply

By Deborah - July 2, 2011

I would like to create an editor table (Table 3) to allow selection of a 'listing_number' field (using multiple checkboxes) from a merging of a field in two other tables (Table 1 + Table 2).

Table 1 = Homes
Field = listing_number

Table 2 = Land
Field = listing_number

Table 3 = Featured Listings
all 'listing_number' fields from Tables 1 & 2 are merged into one list field for section from within this new editor table

So difficult to explain. Sorry if it's not clear, but hope there's a MySQL query (advanced) that can be applied to the list options for Table 3 to accomplish this.

Thanks in advance for any help.
~ Deborah

Re: [Deborah] create list field from merged field in two other tables

Archived
Reply
Reply

By Jason - July 4, 2011

Hi Deborah,

You can use mysql UNION SELECT to accomplish this. In this example, we use listing_number as both the value and the label for the checkboxes. This method does assume that listing_number is a unique value across both tables.

SELECT merged.listNumValue, merged.listNumLabel
FROM (SELECT listing_number AS listNumValue, listing_number AS listNumLabel
      FROM `<?php echo $TABLE_PREFIX;?>table_1`
      UNION SELECT listing_number, listing_number
      FROM `<?php echo $TABLE_PREFIX;?>table_2`) AS merged
GROUP BY merged.listNumValue

Hope this helps get you started

---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/