Impossible query?

  • Thread starter Thread starter Gordon Currie
  • Start date Start date
G

Gordon Currie

Hi:

I am trying to create a query that may not even be possible.

Products table has an ID (PK) and CODE. Related products table has PROD_ID
(original product ID) and RELPROD_ID (related product ID). Both
related.PROD_ID and related.RELPROD_ID are joined to products.ID.

I want to create a query that returns the products.CODE for each entry in the
related products table (related.PROD_ID and related.RELPROD_ID). So far, I
have run up against the limitations of nesting and my own query creation
abilities.

Is this even doable?

thanks - Gordon
 
If I understand your question correctly, you might try a query whose SQL
looks something like this:

SELECT
products.PROD_ID,
products.CODE,
related.RELPROD_ID,
self.CODE AS RELCODE
FROM
(products
INNER JOIN
related
ON
products.PROD_ID = related.PROD_ID)
INNER JOIN
products AS self
ON
related.RELPROD_ID = self.PROD_ID
 
Gordon Currie said:
Hi:

I am trying to create a query that may not even be possible.

Products table has an ID (PK) and CODE. Related products table has PROD_ID
(original product ID) and RELPROD_ID (related product ID). Both
related.PROD_ID and related.RELPROD_ID are joined to products.ID.

I want to create a query that returns the products.CODE for each entry in the
related products table (related.PROD_ID and related.RELPROD_ID). So far, I
have run up against the limitations of nesting and my own query creation
abilities.

Is this even doable?
Click to expand...

Yes, this should be easily doable. I'm assuming you two separate one-to-many
relationships going from the "Products" to "Related"? If that's the case, do
not include both relationships in your query. Just include the relationship
that goes from ID to PROD_ID.

To remove one of the relationships in the query grid, just right-click on
the relationship line and choose "Delete". Caution: perform this deletion in
the query designer, NOT in the table relationships window!

Your resulting SQL will look something like this:

SELECT Related.PROD_ID, Related.REL_PRODID, Products.CODE
FROM Products INNER JOIN Related ON Products.ID = Related.PROD_ID;

I've made some inferences based on your description. If this reply doesn't
help, please post back with the SQL of your query as it's currently written,
so I can understand better what you're trying to do.

Mitch
 
Please disregard my previous post.

I just realized you're trying to display TWO product codes per result row in
the query (one for the "main" product and one for the related product).

In that case you'll need to take a different approach, using what are called
scalar subqueries. Your SQL will look something like this:

SELECT Related.PROD_ID, Related.REL_PRODID, (SELECT CODE FROM Products WHERE
PRODUCTS.ID = PROD_ID) AS PROD_ID_CODE, (SELECT CODE FROM Products WHERE
PRODUCTS.ID = REL_PRODID) AS REL_PRODID_CODE
FROM Related;

I'm assuming the field names in "Products" and "Related" aren't duplicated
between the two tables. If that's not true, let me know, some tweaking of
the SQL will be necessary.

Mitch
 
Brian:

This was just what I needed. I adjusted the names as needed and made the
whole thing into an APPEND query, like so:

INSERT INTO temprelprod ( PROD_CODE, RELPROD_CODE )
SELECT products.CODE AS PROD_CODE, self.CODE AS RELPROD_CODE
FROM (products INNER JOIN relprod ON products.ID = relprod.PRODUCT_ID)
INNER JOIN products AS self ON relprod.RELPROD_ID = self.ID;

Works a treat and saved me hours. I appreciate it!

-Gordon
 
Mitch:

Thanks, this works well too. I ended up using the similar suggestion from
Brian Camire. I converted it to an APPEND query:

INSERT INTO temprelprod ( PROD_CODE, RELPROD_CODE )
SELECT products.CODE AS PROD_CODE, self.CODE AS RELPROD_CODE
FROM (products INNER JOIN relprod ON products.ID = relprod.PRODUCT_ID)
INNER JOIN products AS self ON relprod.RELPROD_ID = self.ID;

I had never heard of scalar subqueries before; this is interesting!

Thanks a lot for your help - Gordon
 
Back
Top