Databases
StackOverflow2013
Postsdbo
POPossible to reference two specifc tables with one query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPossible to reference two specifc tables with one query
    primarykey
    data
    text
    <p>This already involves four INNER JOINs and one LEFT JOIN, but I am not sure how I can reference one more table that is similar to the LEFT JOIN table.</p> <p>Here is my current query which works as it is suppose to</p> <pre><code> SELECT xp.productid, xp.product, xc.classid, xco.optionid, xco.option_name, xiW.id, xiW.image_path, count(xvi.variantid) as cnt FROM xcart_products xp INNER JOIN xcart_variants xv ON xp.productid = xv.productid INNER JOIN xcart_variant_items xvi ON xv.variantid = xvi.variantid INNER JOIN xcart_class_options xco ON xvi.optionid = xco.optionid INNER JOIN xcart_classes xc ON xco.classid = xc.classid AND xc.class = 'COLOR' LEFT JOIN xcart_images_W xiW ON xiW.id = xvi.variantid GROUP BY xco.optionid ORDER by xp.product DESC </code></pre> <p>So essentially each product has both a class and a variant, then each class has class options, and each variant has variant items. The variants have their own variant image and each class has it's own 'detailed' image (xcart_images_D). As of now I am referencing the variant images but what I would like to do is instead of counting xvi.variantid I want to count how many detailed images there are for each of optionid.</p> <p>I have a query which will do this but unfortunately I cannot use this query to pull xcart_images_W. That query is belo</p> <pre><code> SELECT xp.productid, xp.product, xc.classid, xco.optionid, xco.option_name, xiD.image_path, xiD.path_on_server, count(xiD.optionid) as cnt FROM xcart_products xp INNER JOIN xcart_classes xc ON xp.productid = xc.productid AND xc.class = 'Color' INNER JOIN xcart_class_options xco ON xc.classid = xco.classid LEFT JOIN xcart_images_D xiD ON xiD.optionid = xco.optionid GROUP BY xp.product, xco.optionid ORDER by xp.product DESC </code></pre> <p>Is it possible to reference these two tables or do their foreign keys make it impossible to do so?</p> <p>Below I have provided the table structures.</p> <pre><code>+ xcart_products - productid* - product + xcart_variants - variantid* - productid (xcart_products.productid) + xcart_variant_items [bridge table] - optionid* - variantid (xcart_variants.variantid) + xcart_classes - classid* - productid (xcart_products.productid) - class + xcart_class_options - optionid* - option_name - classid (xcart_classes.classid) + xcart_images_W - imageid* - id (xcart_variants.variantid) - image_path + xcart_images_D - imageid* [not relational with xcart_images_W.imageid] - id (xcart_products.productid) - optionid (xcart_class_options.optionid) - image_path * Primary Key () relational data [] notes </code></pre> <p>Currently working but messy query. How do I clean up?</p> <pre><code> SELECT xp.productid, xp.product, xc.classid, xco.optionid, xco.option_name, xiW.id, xiW.image_path FROM xcart_products xp INNER JOIN xcart_variants xv ON xp.productid = xv.productid INNER JOIN xcart_variant_items xvi ON xv.variantid = xvi.variantid INNER JOIN xcart_class_options xco ON xvi.optionid = xco.optionid INNER JOIN xcart_classes xc ON xco.classid = xc.classid AND xc.class = 'COLOR' LEFT JOIN xcart_images_W xiW ON xiW.id = xvi.variantid LEFT JOIN ( SELECT COUNT(xiD.optionid) as dCount FROM xcart_products xp2 INNER JOIN xcart_classes xc2 ON xp2.productid = xc2.productid AND xc2.class = 'Color' INNER JOIN xcart_class_options xco2 ON xc2.classid = xco2.classid LEFT JOIN xcart_images_D xiD ON xiD.optionid = xco2.optionid ) ON xiW.id = xvi.variantid GROUP BY xco.optionid ORDER by xp.product DESC </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USEric
    1. USEric
    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
    1. VO
      singulars
      1. POPossible to reference two specifc tables with one query
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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