Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2013

    Unanswered: Multiple table query needing 1 answer result


    I have three tables. One is called "asset" which holds item asset names, one is called "catalog" which just defines catalog list names, and a third called "catalog_asset" which combines the other two and makes it possible to contain assets within the catalog.

    Right now I AM able to execute the following query to determine all of the assets inside of the catalog. This query works!
    select name from asset where id in (select asset_id from catalog_asset where catalog_id='SHOP_1_LIST');

    The result is something like:
    ITEM 1
    ITEM 2
    and so on.

    However, what I now want is kind of reverse be able to query 1 asset (from table 'asset') from and to display the catalog name in which it resides (from table catalog_asset).

    I tried reversing the above query in a way I thought it would work, but it did not work at all...
    select catalog_id from catalog_asset where exists (select name from asset where id = '2740');

    I want the result to show which catalog the asset resides in (can be multiple):

    Basically I can query which assets are residing in a catalog, but I am unable to query an asset to see which catalog it resides in.

    I am currently working on a tool made in php and I need to display the catalog name.

    Can someone help me?

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    without knowing your table design and column names its hard to answer

    ideally you should use JOINS,but you can use the deprecated THETA syntax

    select my, column, list from catalog
    join catalog_asset on catalog.columnwhatever = catalog_asset.hereyougo
    where catalog_asset.theassetid = 2740

    theta style (shouldn't be used for modern code as its deprecated)
    select my, column, list from catalog
    where catalog.columnwhatever = catalog_asset.hereyougo and catalog_asset.theassetid = 2740

    you will have to chaneg the column names as rquired to fit your secret design
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2013
    I was able to create two queries that I wanted. So the original question to this answer is resolved. But now I have one more issue.

    Any idea on how to join two select queries from two different tables as one? If I don't limit it to one query, I will have to change all of my PHP to allow for multiple queries.

    Query 1 (from asset table):
    SELECT path_url,price from asset where name = 'item-1';

    Result is displaying the information in the specified fields. It works fine.

    Query 2 (from catalog table):
    select catalog_id from catalog_asset where asset_id = (select id from asset where name = 'item-1');

    Result is simple - the name of the catalog in which the item resides. It works fine.

    Ideally, I would need one query that contains both of these queries, giving an output of the field information of the item (from table 1) and the catalog in which it resides (table 2).

    I have tried INNER JOIN and CROSS JOIN and both just ran an insane query of the entire asset table along with the entire catalog table...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts