Simple query question.

[dcjeans]

I am having a hard time creating a simple query.

I have two tables. "last_count" which has the columns "barcode" "location", and "count"
and "slow" which has columns "barcode" and "description".

The basic idea, is that "last_count" contains an inventory count of my entire catalog with seperate counts in different locations. "slow" is a list of slow moving items that I need to collect and remove from my inventory.

I came up with the following query to tell me where each item was, but there is a problem.

select RIGHT(S.barcode, 6), S.description, (select location from last_count where RIGHT(barcode,6) = RIGHT(S.barcode,6)) Location from slow s ;

That works, to a degree, as long as each item is only in one location. But, if widgetA is being stored in two different locations, that query only shows me the count at the first location. The second location is ignored.

Can anyone help me modify this query so that I get the counts from all locations where widgetA is stored?

thanks in advance.

Darron

 

[dcjeans]

Oops,

I listed the wrong query.

Here is the one that sorta works.

select RIGHT(S.barcode, 6), S.description, (select location from last_count where RIGHT(barcode,6) = RIGHT(S.barcode,6)) Location, (select stock from last_count where RIGHT(barcode,6) = RIGHT(S.barcode,6)) Count from slow s ;

 

[JarlH]

I'm not quite sure I get what you mean, but I suppose what you are looking for is something like this:

SELECT barcode, description, count, location
FROM last_count l
WHERE l.barcode IN (SELECT barcode FROM slow)


If an item is found in the slow table, this query will return the data from last_count for that item.

Note that COUNT is a reserved word in SQL-92, but not not in SQL-99 or SQL-2003. (Just in the case you run into trouble...)

 

[dcjeans]

Thanks for the reply,

That is almost what I am needing, I think. Except, the description is in the other table (slow) - the last_count table does not contain the description.

I modified the query as follows;

(I am only using the right 6 numbers of the barcode because the "slow" table was imported from excel, which strip leading zeros from some of the barcodes, and that was the only way I could figure around that)

SELECT RIGHT(barcode,6), stock, location FROM last_count l WHERE RIGHT(l.barcode,6) IN (SELECT RIGHT(barcode,6) FROM slow)

It works, but there is no description with it. I haven't been able to figure out how to get the description in there too. Any ideas?

thanks again.

Darron

 

[JarlH]

Try this:

SELECT RIGHT(barcode,6), stock, location,
(SELECT description FROM slow s where s.barcode = l.barcode)
FROM last_count l
WHERE RIGHT(l.barcode,6) IN (SELECT RIGHT(barcode,6) FROM slow)

 

[dcjeans]

Strange,

using that query, 5 out of 316 columns had descriptions with them. the rest were blank.

hmmm

 

[dcjeans]

OK,

I think I got it!!

SELECT RIGHT(barcode,6), stock, location, (SELECT description FROM slow s where RIGHT(s.barcode,6) = RIGHT(l.barcode,6)) FROM last_count l WHERE RIGHT(l.barcode,6) IN (SELECT RIGHT(barcode,6) FROM slow)

Seems to work.

Thanks again for the help!!!

Couldn't have done it without you ; )

Darron

 

[PHV]

And what about simply this ?
SELECT RIGHT(l.barcode,6), l.stock, l.location, s.description
FROM last_count l, slow s
WHERE RIGHT(l.barcode,6) = RIGHT(s.barcode,6)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dcjeans]

That worked as well!!!

Thanks!

You guys have been a big help.

Darron

 

[JarlH]

PH,

That was almost too simple...