SQL query assistance :o)

D

dan.gates

Can someone please assist me in fine tuning this query?

My ulitmate goal is to get the first 50 results back from the [15]
columns where the field is 0 (zero).


Here is the SQL statement as it now...

SELECT V1.[item_desc],
V1.[size],V1.[uom_desc],V1.[p_­­upc_alpha],V1.[ag_item_cd],v1­.­[status_cd],v1.[avg_mvmt],C1­.[­15],



'CEREAL' AS Sub_Category
FROM Cereal AS C1
LEFT JOIN v_item_schematics AS V1 ON C1.AG = V1.ag_item_cd

WHERE 50 >= (SELECT COUNT(AG) FROM Cereal
LEFT JOIN v_item_schematics ON Cereal.AG = v_item_schematics.ag_item_cd

WHERE v_item_schematics.avg.mvmt > v1.avg_mvmt)

UNION ALL

SELECT V1.[item_desc],
V1.[size],V1.[uom_desc],V1.[p_­­upc_alpha],V1.[ag_item_cd],v1­.­[status_cd],v1.[avg_mvmt],S1­.[­15],


'SOUP' AS Sub_Category
FROM Soup AS S1
LEFT JOIN v_item_schematics AS V1 ON S1.AG = V1.ag_item_cd

WHERE 50 >= (SELECT COUNT(AG) FROM Soup
LEFT JOIN v_item_schematics ON Soup.AG = v_item_schematics.ag_item_cd
WHERE v_item_schematics.avg.mvmt > v1.avg_mvmt)

order BY Sub_category,v1.[avg_mvmt] DESC;


Right now the results are 925 records, 243 being from the first table
and 682 from the second, instead of just a total of 100.

Thanks to everyone and I appreciate all the learning this group is
teaching me!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL query assistance :o) 0
help with Access SQL query :o) 3
SQL Union Query needs fine tuning :o) 0
SQL Query Help? 3
Top property for multiple columns? 5
Union Query SQL 2
Gaps in ordered series 1
Need helps on my union query 3

Top