Databases
StackOverflow2013
Postsdbo
POHow to exclude rows where matching join is in an SQL tree
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to exclude rows where matching join is in an SQL tree
    primarykey
    data
    text
    <p><strong>Update:</strong> After playing around with this for a few hours, went with a multi-query solution and used a table that only contained parent attributes to determine which items needed updating.</p> <hr> <p>Sorry for the poor title, I couldn't think how to concisely describe this problem.</p> <p>I have a set of items that should have a 1-to-1 relationship with an attribute.</p> <p>I have a query to return those rows where the data is wrong and this relationship has been broken (1-to-many). I'm gathering these rows to fix them and restore this 1-to-1 relationship.</p> <p>This is a theoretical simplification of my actual problem but I'll post example table schema here as it was requested.</p> <p><code>item</code> table:</p> <pre><code>+------------+------------+-----------+ | item_id | name | attr_id | +------------+------------+-----------+ | 1 | BMW 320d | 20 | | 1 | BMW 320d | 21 | | 2 | BMW 335i | 23 | | 2 | BMW 335i | 34 | +------------+------------+-----------+ </code></pre> <p><code>attribute</code> table:</p> <pre><code>+---------+-----------------+------------+ | attr_id | value | parent_id | +---------+-----------------+------------+ | 20 | SE | 21 | | 21 | M Sport | 0 | | 23 | AC | 24 | | 24 | Climate control | 0 | .... | 34 | Leather seats | 0 | +---------+-----------------+------------+ </code></pre> <p>A simple query to return items with more than one attribute.</p> <pre><code>SELECT item_id, COUNT(DISTINCT(attr_id)) AS attributes FROM item GROUP BY item_id HAVING attributes &gt; 1 </code></pre> <p>This gets me a result set like so:</p> <pre><code>+-----------+------------+ | item_id | attributes | +-----------+------------+ | 1 | 2 | | 2 | 2 | | 3 | 2 | -- etc. -- </code></pre> <p>However, there's an exception. The <code>attribute</code> table can hold a tree structure, via parent links in the table. For certain rows, <code>parent_id</code> can hold the ID of another attribute. There's only one level to this tree. Example:</p> <pre><code>+---------+-----------------+------------+ | attr_id | value | parent_id | +---------+-----------------+------------+ | 20 | SE | 21 | | 21 | M Sport | 0 | .... </code></pre> <p>I <strong>do not</strong> want to retrieve items in my original query where, for a pair of associated attributes, they related like attributes 20 &amp; 21.</p> <p>I <strong>do</strong> want to retrieve items where:</p> <ul> <li>the attributes have no parent</li> <li>for two or more attributes they are not related (e.g. attributes 23 & 34)</li> </ul> <p>Example result desired, just the item ID:</p> <pre><code>+------------+ | item_id | +------------+ | 2 | +------------+ </code></pre> <p>How can I join against <code>attributes</code> from <code>items</code> and exclude these rows?</p> <p>Do I use a temporary table or can I achieve this from a single query?</p> <p>Thanks.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USGreg K
    1. USGreg K
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to exclude rows where matching join is in an SQL tree
      1. USPatrick
      1. VTFavorite
    2. VO
      singulars
      1. POHow to exclude rows where matching join is in an SQL tree
      1. This table or related slice is empty.
      1. VTUpMod
    1. COcan you add a sample data and structure for "items", and how it relates to "attributes" in all 3 scenarios: clean data (1-to-1), dirty (1-to-many), and the exception case?
      singulars
      1. POHow to exclude rows where matching join is in an SQL tree
      1. USSunny Milenov
    2. COThese are all theoretical simplifications of the actual system. I'll add some examples when I get home.
      singulars
      1. POHow to exclude rows where matching join is in an SQL tree
      1. USGreg K
    3. COIs there only 1 level to this tree, or n? If n, I think you need a recursive solution - which makes it harder.
      singulars
      1. POHow to exclude rows where matching join is in an SQL tree
      1. USMark Brackett
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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