StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL Select JOIN multiple Tables
text
Body
copied!<p>I essentially have two groups of tables. 'props_' and 'avail_'. Each with approx. 4 tables which i have joined using UNION ALL. </p> <p>I need to select the properties with the necessary location & sleeps which don't appear in the availability tables for specific date ranges.</p> <pre><code>property tables... 'props_01', 'props_02', 'props_03', 'props_04' id code location sleeps 1 7A06E91875 devon 2 2 440ac2664c cornwall 4 5 0474d4882b devon 2 availability tables... 'avail_01', 'avail_02', 'avail_03', 'avail_04' id prop_code date 1 440ac2664c 20130208 2 440ac2664c 20130209 3 440ac2664c 20130210 4 440ac2664c 20130211 5 440ac2664c 20130212 </code></pre> <p>These <strong>availability tables contain all the booked dates</strong>. So i wish to only select from the property tables where they don't have an availability record for the specified date range. This is how i was looking to do query it... </p> <pre><code>(avail.date != '20130209' AND avail.date != '20130210' AND avail.date != '20130211' AND avail.date != '20130212' AND avail.date != '20130213') OR (avail.date != '20130210' AND avail.date != '20130211' AND avail.date != '20130212' AND avail.date != '20130213' AND avail.date != '20130214') </code></pre> <p>I also need to apply filters to the property tables for the location and sleeps.</p> <p>The query i have which does everything apart from including the availabity is below, this works fine, but i don't know <strong>how best to modify it to also query the availability tables</strong>...</p> <pre><code>SELECT prop.* FROM (SELECT * FROM `props_01` UNION ALL SELECT * FROM `props_02` UNION ALL SELECT * FROM `props_03` UNION ALL SELECT * FROM `props_04`) prop WHERE prop.location='Devon' AND prop.sleeps>='4' ORDER BY prop.sleeps ASC </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload