How do LEFT JOINS work?

[jjjt]

Hi,

I have a table of products, some of which are held at remote stock locations. If they are an entry is made in a table, remotestockquantities, storing the id of the product (rsq_prod_id) and the id of the remote stock location (rsq_rsl_id) along with the quantity held (rsq_qty). If a product is not held at the remote stock location there is no entry for that product in the remotestockquantities table. It is also possible for a product to be held at more than one remote stock location.

What I want to do is get a list of all products that are NOT held at a particular stock location. In other words I need a list that contains all products that are not held at any remote stock location plus all those that are held at a location different to the one specified.

I thought I would be able to do this using a LEFT JOIN but I am getting confusing results. If I run the following query:

SELECT product.id, remotestockquantities.rsq_rsl_id;
FROM product;
LEFT JOIN remotestockquantities;
ON product.id == remotestockquantities.rsq_prod_id;
WHERE remotestockquantities.rsq_rsl_id = 7;
INTO CURSOR selected_prods;
ORDER BY id

I get one record in my cursor, which is correct since there is only one product held in stock location 7.

If I run the following query, (exactly the same but with != instead of =) however:

SELECT product.id, remotestockquantities.rsq_rsl_id;
FROM product;
LEFT JOIN remotestockquantities;
ON product.id == remotestockquantities.rsq_prod_id;
WHERE remotestockquantities.rsq_rsl_id ! = 7;
INTO CURSOR selected_prods;
ORDER BY id

I get no records returned to my cursor and I was expecting to get all products except the one that was returned by my previous query. Initially I thought this was because rsq_rsl_id we being populated in the cursor with a NULL value for all products where there was no record in the remotestockquantities table, so I changed my query to be:

SELECT product.id, remotestockquantities.rsq_rsl_id;
FROM product;
LEFT JOIN remotestockquantities;
ON product.id == remotestockquantities.rsq_prod_id;
WHERE remotestockquantities.rsq_rsl_id != 7;
OR ISNULL(rsq_rsl_id);
INTO CURSOR selected_prods;
ORDER BY id

However, this returned all products in the database including the one where rsq_rsl_id = 7 in the remotestockquantities table.

So I am in a situation where I can either return all products from the database or none. I can return a subset if I use 'equals' in my WHERE clause but not if I use 'does not equal'. I am now wondering whether my understanding of LEFT JOINS is just wrong or whether there is a restriction on the use of LEFT JOINS. Is it possible that you cannot use ! = with LEFT JOINS? If so, is there an alternative way of achieving my goal of getting a list of all products not held at a particular stock location? Any help would be much appreciated.

 

[ramani]

Hi


1. What I want to do is get a list of all products that are NOT held at a particular stock location.

2. In other words I need a list that contains all products that are not held at any remote stock location plus all those that are held at a location different to the one specified.

These are two requirements.

1. All products which are not held in a particular location..
SELECT product.id ;
FROM product ;
WHERE product.id NOT IN ;
(SELECT rsq_prod_id FROM remotestockquantities ;
WHERE remotestockquantities.quantity # 0)

I have assumed that there could be remoteStockIds with ZERO balances also and they need to be removed. Otherwise the last WHERE condition can be removed.

2. If you want to add to the above all items, that are not in the first, but only on the second.. then..

SELECT product.id ;
FROM product ;
WHERE product.id NOT IN ;
(SELECT rsq_prod_id FROM remotestockquantities ;
WHERE remotestockquantities.quantity # 0) ;
UNION ALL ;
SELECT rsq_prod_id ;
FROM remotestockquantities ;
WHERE rsq_prod_id NOT IN ;
(SELECT product.id FROM product ;
WHERE product.quantity # 0)
ORDER BY 1

Again the where conditions of sub selects can be ignored, if there is no zero stock checking required.
You can add other fields such as balances etc.. in the main SELECTS to suit your requirement.



ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/