Joshua Wieczorek Joshua Wieczorek - 1 year ago 85
MySQL Question

SQL Select Where Join Table

Is there a way to do a select to do something like the following without running tow queries? The first query getting the "id" if the a column and the second queuing another table returning where that "id" matches?

SELECT `name`
FROM `attributes`
INNER JOIN `attribute_vals`
ON `attributes`.`id`=`attribute_vals`.`attr_id`
WHERE `name`='weight'

Thank you for your answers!

Just a quick graphical representation of what I am trying to achieve!

enter image description here

Answer Source

Making a few assumptions about your DB design: 1. the name column is in the attribute_vals table, 2. the attr_id, name combination is unique, and 3. there's a val column in teh attribute_vals table.

It sounds like you want to aggregate on your column.

You can then get associated attribute_vals data with MAX functions.

    `a`.`id` AS `attr_id`,
    MAX(`av`.`id`) AS `val_id`,
    MAX(`av`.`name`) AS `name`,
    MAX(`av`.`val`) AS `val`
FROM `attributes` AS `a`
INNER JOIN `attribute_vals` AS `av`
    ON `a`.`id`=`av`.`attr_id`
    AND `name`='weight'
GROUP BY `attributes`.`id`

What's happening here is that we're getting every attribute that has a 'weight' value associated with it, only presenting a single result per associated attribute.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download