SQL Query Help?

[Lachlan Mullen]

an someone please assist me in fine tuning this query?

My ultimate 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!

 

[Guest]

I would suggest that you stop reposting the question. This is Post #6. And
you've also gone to the trouble of attempting to be devious by going to a 3rd
name.

Your question is undoubtedly important to you. However, the people here are
volunteers. Therefore, if you are polite and follow the rules, your questions
get answered.

Otherwise, I suspect they may not. The gurus who frequent this ng will not
take kindly to your rudeness.

 

[Dan]

Again, please see prior posting...there were issues with the groups
yesterday posting. This was not done by me, but a co-worker, to see if
his posts would go through either.

Before you claim rudeness, perhaps you could have researched for a
minute at past posts made by myself and others to see what history
there truly was with their ID. Does this person spam? Are they rude?
Before jumping to conclusions. Hmm? Maybe their was some server issue
on the other end, that is why the multiple postings came through?!?

I have always aknowledged my graciousness for the time others put into
helping those of us with questions. I again thank everyone who assists
and hope we can move forward from here.

Regards, Dan

 

[Guest]

Accept most humble apologies. I did check before I wrote and the only history
that I saw for you or your co-worker consisted of this one query (and one
response from you). I considered the possibility that you were new to news
groups- that would account for the impatience at not getting an instantaneous
response- but I must admit that, regrettably, I didn't think about server
problems.

Once again, apology submitted. And in return, thanks for your restrained
response.