query problem

[Tony Rogerson]

SELECT top5.PRODID, p.PRODNAME
FROM (
SELECT TOP 5 SALES = COUNT(*), PRODID
FROM SALES
GROUP BY PRODID
ORDER BY SALES DESC ) AS top5
INNER JOIN PRODUCTS p ON p.PRODID = top5.PRODID

ORDER BY top5.SALES DESC

--
Tony Rogerson
SQL Server MVP
[url]http://www.sqlserverfaq.com?mbr=21[/url]
(Create your own groups, Forum, FAQ's and a ton more)

[Jacco Schalkwijk]

SELECT TOP 5 P.PRODNAME, COUNT(*) AS total_sales
FROM PRODUCTS P
INNER JOIN SALES s
ON P.PRODID = s.PRODID
GROUP BY P.PRODNAME
ORDER BY total_sales DESC

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


"He Sha" <hesha@hotmail.com> wrote in message
news:eE5k3w4RDHA.3700@tk2msftngp13.phx.gbl...

> 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.
>
>
>

[He Sha]

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.

[Poppy]

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

[Chris Hohmann]

"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

[Bullschmidt]

And you can use Not In within a query:

[url]http://dbforums.com/showthread.php?postid=3050009#post3050009post30500 09[/url]

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


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

[tejun]

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

[newuser111]

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 ???

[Steve Grosz]

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

[mpwoodward *TMM*]

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

[Steve Grosz]

So, if I enter it like this:

<cfquery name="test" datasource="Test3">
select
specs.ProdImage,model.Model,specs.ProdStroke,specs .ProdCC,specs.ProdFuel,
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

I still have the same problem. A 'Element SPECID is undefined in SPECS'
error message

Steve
"mpwoodward *TMM*" <mpwoodward@gmail.com> wrote in message
news:d4e0vm$gc6$1@forums.macromedia.com...

> 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