Re: query problem

Hi all,

There is a table called SALES with a single field called PRODID. An entry is
made into this everytime there is a sale.

Doing a SELECT TOP 5 COUNT(*) C, PRODID FROM SALES GROUP BY PRODID ORDER BY
C DESC gives me the list of top 5 selling products.

In another table PRODUCTS, there are two fields PRODID, PRODNAME. Is there a
way to find out the names of top 5 selling products using a single query?

Thanks,
HS.

I have 2 tables, each which contain usernames and id's.

I need to extract all users from tableA which do not appear in tableB but
cant figure out the syntax of the query.

Any Ideas ?

Thanks in advance

"Poppy" <paul.diamond@NOSPAMthemedialounge.com> wrote in message
news:OJTpog69DHA.4020@TK2MSFTNGP09.phx.gbl...

> I have 2 tables, each which contain usernames and id's.
>
> I need to extract all users from tableA which do not appear in tableB

but

> cant figure out the syntax of the query.
>
> Any Ideas ?

Database? Version? DDL? Sample Data?

Here's a pseudo query since I have no idea what your database looks
like:

SELECT
A.username,
A.id
FROM
tableA AS A LEFT JOIN
tableB AS B ON
A.username = B.username AND
A.id = B.id
WHERE
B.id IS NULL

HTH
-Chris Hohmann

And you can use Not In within a query:

http://dbforums.com/showthread.php?p...009post3050009

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

When you use Order_By, you must include all fields from your query. You can use the MS Access Upsize tool OR use SQL Server Enterprise Manager Database Tranformation Services (DTS).

-- lnh

thx for your answer.

Do you know where i can get basic info on how to migrate from access to sql
server?
do i need to change the code of the queries contained in my cfm and cfc files?
what are these changes ???

I have 2 seperate queries:

1)
select *
from vendor
left outer join model
on vendor.PID=model.VendorID
left outer join specs
on model.Model=specs.ProdModel
where vendor.Vendor='#URL.Vendor#'
order by Model

2)
SELECT specs.SpecID
from vendor
left outer join model
on vendor.PID=model.VendorID
left outer join specs
on model.Model=specs.ProdModel
where vendor.Vendor='TGB'

So, why am I getting a error saying that a 'element SPECID is undefined in
specs' when it works with query #2 when I put:

select *, specs.SpecID
from vendor
left outer join model
on vendor.PID=model.VendorID
left outer join specs
on model.Model=specs.ProdModel
where vendor.Vendor='#URL.Vendor#'
order by Model

Steve Grosz wrote:

> So, why am I getting a error saying that a 'element SPECID is undefined in
> specs' when it works with query #2 when I put:
>
> select *, specs.SpecID
> from vendor
> left outer join model
> on vendor.PID=model.VendorID
> left outer join specs
> on model.Model=specs.ProdModel
> where vendor.Vendor='#URL.Vendor#'
> order by Model

Just a quick guess, but have you tried specifying the tables in your *
select? e.g. select vendor.*, specs.SpecID from vendor ... I'd give
that a shot first and see if that makes any difference.

Matt

--
Matt Woodward
Team Macromedia - ColdFusion