I have the following query. Here is how it works, there are multiple tables. When the initial record is made, its data/columns are stored "shorthand/url friendly". When I pull them with this query it queries the shorthand for an equivelent in a "definition" table for the long/full version.
PHP Code:
$q = vB::$db->query_first("
SELECT `price`,
`obo`,
`offer`,
`state`,
`location` AS `location_org`,
(SELECT `long` FROM " . TABLE_PREFIX . "`thread_classifieds_locations` WHERE `short` = " . TABLE_PREFIX . "`thread_classifieds`.`location` && " . TABLE_PREFIX . "`thread_classifieds`.`threadid` = " . $threadid . ") AS `location_full`,
`area`,
(SELECT `long` FROM " . TABLE_PREFIX . "`thread_classifieds_posttype` WHERE `short` = " . TABLE_PREFIX . "`thread_classifieds`.`posttype` && " . TABLE_PREFIX . "`thread_classifieds`.`threadid` = " . $threadid . ") AS `posttype`,
(SELECT `long` FROM " . TABLE_PREFIX . "`thread_classifieds_posttype_firearm` WHERE `short` = " . TABLE_PREFIX . "`thread_classifieds`.`posttype_firearm` && " . TABLE_PREFIX . "`thread_classifieds`.`threadid` = " . $threadid . ") AS `posttype_firearm`,
(SELECT `id` FROM " . TABLE_PREFIX . "`thread_classifieds_posttype_firearm` WHERE `short` = " . TABLE_PREFIX . "`thread_classifieds`.`posttype_firearm` && " . TABLE_PREFIX . "`thread_classifieds`.`threadid` = " . $threadid . ") AS `typenum`,
(SELECT `long` FROM " . TABLE_PREFIX . "`thread_classifieds_caliber` WHERE `short` = " . TABLE_PREFIX . "`thread_classifieds`.`caliber` && " . TABLE_PREFIX . "`thread_classifieds`.`threadid` = " . $threadid . " && " . TABLE_PREFIX . "`thread_classifieds_caliber`.`type` = `typenum`) AS `caliber`,
(SELECT `long` FROM " . TABLE_PREFIX . "`thread_classifieds_manufacturer` WHERE `short` = " . TABLE_PREFIX . "`thread_classifieds`.`manufacturer` && " . TABLE_PREFIX . "`thread_classifieds`.`threadid` = " . $threadid . " && " . TABLE_PREFIX . "`thread_classifieds_manufacturer`.`type` = `typenum`) AS `manufacturer`,
(SELECT `long` FROM " . TABLE_PREFIX . "`thread_classifieds_action` WHERE `short` = " . TABLE_PREFIX . "`thread_classifieds`.`action` && " . TABLE_PREFIX . "`thread_classifieds`.`threadid` = " . $threadid . " && " . TABLE_PREFIX . "`thread_classifieds_action`.`type` = `typenum`) AS `action`,
(SELECT `long` FROM " . TABLE_PREFIX . "`thread_classifieds_type` WHERE `short` = " . TABLE_PREFIX . "`thread_classifieds`.`type` && " . TABLE_PREFIX . "`thread_classifieds`.`threadid` = " . $threadid . " && " . TABLE_PREFIX . "`thread_classifieds_type`.`type` = `typenum`) AS `type`
FROM " . TABLE_PREFIX . "`thread_classifieds`
WHERE `threadid` = " . $threadid . " LIMIT 1
");
The query as-is, works. but I feel it can be simplified. Just not sure how.
--------------- Added [DATE]1360626868[/DATE] at [TIME]1360626868[/TIME] ---------------
ended up figuring it out
PHP Code:
$q = vB::$db->query_first("
select c.`price`,
c.`obo`,
c.`offer`,
c.`state`,
c.`location` AS `location_org`,
l.`long` AS `location_full`,
c.`area`,
p.`long` AS `posttype`,
pf.`long` AS `posttype_firearm`,
pf.id AS `typenum`,
cc.`long` AS `caliber`,
m.`long` AS `manufacturer`,
a.`long` AS `action`,
t.`long` AS `type`
from thread_classifieds c
left join thread_classifieds_locations l
on c.location = l.short
left join thread_classifieds_posttype p
on c.posttype = p.short
left join thread_classifieds_posttype_firearm pf
on c.posttype_firearm = pf.short
left join thread_classifieds_caliber cc
on c.caliber = cc.short
and pf.id = cc.`type`
left join thread_classifieds_manufacturer m
on c.manufacturer = m.short
and pf.id = m.`type`
left join thread_classifieds_action a
on c.action = a.short
and pf.id = a.`type`
left join thread_classifieds_type t
on c.type = t.short
and pf.id = t.`type`
where c.`threadid` = 72 LIMIT 1;
");
More Information