Select with sub-query.

[petrosky]

Hi there,

I am trying to query a products table that lives on ServerA for products that have > 0 inventory and sub query a transaction table on ServerB for sales in the last six months.

Basically I want to display products that we have on hand that havn't sold in 6 months (bad retail!)

Here is my attempt at the query.

SELECT DISTINCT d3sync.d3_import.ivmst_id, d3sync.d3_import.rsv_desc FROM d3sync.d3_import WHERE inv > 0 AND d3sync.d3_import.ivmst_id NOT IN (SELECT translog.translog.Prod FROM translog.translog WHERE `Date` >= '2009-08-12 12:33:59')

I have spot checked the results and find that I have actually sold some of the returned products in the last six months.

Can anyone help to isolate the problem? Please let me know if any clarification is required.

Thanks for any help you can offer.

Peter.

Remember- It's nice to be important,
but it's important to be nice

 

[r937]

what exactly is the problem?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[petrosky]

Sorry Rudy,

It's probably as clear as mud!

I only want to see products that we have on stock that have NOT sold in the last six months.

The above query returns some products that actually HAVE sold.

Remember- It's nice to be important,
but it's important to be nice

 

[r937]

i don't understand "sold"

you'll have to explain the purpose/role of the columns you're using in that query

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[petrosky]

Hi,

Thanks for perservering with me on this one.

Here is a short version of the d3_import table. It lists all the products we sell with the quantity we have available.

[b]ivmst_id rsv_desc inventory[/b]
1234        Widget    14
4321        Thingo    7
3214        Thimble   3

Then on the other server I have sales records like this.

[b]Date         Prod    Price[/b]
2009-07-12      Thingo  $10
2010-01-01      Widget  $10
2010-01-01      Thimble $10

In this example I would like to return the product "Thingo" as it has stock on hand and hasn't been sold in the last 6 months.

I hope this clarifies things.

Peter.

Remember- It's nice to be important,
but it's important to be nice

 

[r937]

your original query will work with the following changes

1. remove DISTINCT (it isn't needed)

2. change [red]inv > 0[/red] to [blue]inventory > 0[/blue] (typo?)

3. change [red]d3_import.ivmst_id NOT IN[/red] to [blue]d3_import.rsv_desc NOT IN[/blue]



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon