JOIN: excluding rows from first query from the results returned by second query

Question

The title is probably a bit confusing, but I hope this will be easier to understand:

The dashboard is setup like this:

1. user enters a product_id (single or multiple) and the input is saved in a variable 'user_input'

2. the dashboard then returns all customer_ids associated with each product_id and also lists all other product_ids associated with each customer_id.

I'm using a JOIN: first query returns all the product_ids and some other columns. The second query (UNION) uses user_input to return the customer_ids and and the associated product_ids.

This works as expected, but for one detail. I would like to exclude from the results of each customer_id the one product_id that was entered in the dashboard prompt.

Any ideas or hints how to make this happen would be greatly appreciated.

asim cholas · Answer

I would like to exclude from the results of each customer_id the one product_id that was entered in the dashboard prompt.

??

If you have customer and product joined, why cant you simply apply filter on it to get the above result? why need a union?

just drag and drop customer and product and keep prompt for those columns. when you apply a filter for product it will obviously display all customers associated with the product. Similarly when you apply a filter for customer it will show all products associated with the customer.

Thanks

Asim

Wolfrm · Answer

I don't think I can use this quite as you suggest since I have everything in one report, using a UNION JOIN. Filtering like you suggested on the one join did not work.

Wolfrm · Answer

Yes, certainly:

User enters into the dashboard prompt two product_ids: product01;product02

The first query returns product01 and the associated custumer01 etc.

The second query returns customer01 and all other products they ordered, ie. product11,product22,product33.

Or like this:

product_idcustomer_idproduct_id (other products purchased, excluding the product_id from the first column)product01customer01product11,product22,product33product02customer02product66,product77,product88