Please help (complex query)

W

WebRaster

Hi to everyone, I need an helping hand on this question:

I'm bulding an home page of a cars seller, I need to display in this page
nine photos and descriptions taken by the database, 5 of this are fixed BUT
the other 4 elements had to be choosen in database according these select
criteria: the cheapest, the last two cars inserted in database, and the most
clicked in site. In addiction I have to avoid the accidental repetition of
elements on page, for example the ceapest car could be the last inserted in
database so could be displayed twice.

With the same query I have to extract from database these informations:

1) the cheapest one
2) the last two cars inserted in database
3) most clicked

The database structure in this:
Id (key)
product_name (text)
description (Memo)
price (number)
number_of_click (number)
insertion_data (Date)

My question is: how can I create a query that can work to solve this
problem?
--------------------------------
table statement

CREATE TABLE TABELLA1
(id CHAR(5) PRIMARY KEY,
product_name CHAR(20) NOT NULL,
description CHAR(255) NOT NULL,
productor CHAR(50) NOT NULL,
price DECIMAL(7, 2) NOT NULL,
number_of_click INTEGER,
insertion_data DATE NOT NULL);
-----------------------------------------------------
insert statement
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('1', 'Fiat 500 SL', 'etc. etc.', 'FIAT', 3000, '1-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('2', 'Fiat 1500 SQ', 'etc. etc.', 'FIAT', 8200, '10-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('3', 'Lancia Matra', 'etc. etc.', 'Lancia', 6800, '1-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('4', 'Maserati SL', 'etc. etc.', 'Maserati', 13000, '2-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('5', 'Volvo 980', 'etc. etc.', 'Volvo', 9600, '8-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('6', 'Fiat 500 SL', 'etc. etc.', 'FIAT', 9150, '11-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('7', 'Fiat 850 SP', 'etc. etc.', 'FIAT', 7100, '1-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('8', 'Fiat 1500', 'etc. etc.', 'FIAT', 9200, '1-AGO-2003');
--------------------------------------------
I start to try this query:
SELECT top 1 * FROM Tabella1 WHERE id NOT IN (SELECT top 2 * FROM Tabella1
ORDER by id DESC) ORDER BY number_of_click DESC;

and I receive this error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] You have written a subquery that
can return more than one field without using the EXISTS reserved word in the
main query's FROM clause. Revise the SELECT statement of the subquery to
request only one field

What can I have to do
 
M

[MVP] S. Clark

I think I understand that you need to satisfy three unrelated conditions,
but the SQL you posted doesn't seem to jive with that. So, assuming that
the three needs are independent, then I'll recommend using the UNION
operator.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

WebRaster said:
Hi to everyone, I need an helping hand on this question:

I'm bulding an home page of a cars seller, I need to display in this page
nine photos and descriptions taken by the database, 5 of this are fixed BUT
the other 4 elements had to be choosen in database according these select
criteria: the cheapest, the last two cars inserted in database, and the most
clicked in site. In addiction I have to avoid the accidental repetition of
elements on page, for example the ceapest car could be the last inserted in
database so could be displayed twice.

With the same query I have to extract from database these informations:

1) the cheapest one
2) the last two cars inserted in database
3) most clicked

The database structure in this:
Id (key)
product_name (text)
description (Memo)
price (number)
number_of_click (number)
insertion_data (Date)

My question is: how can I create a query that can work to solve this
problem?
--------------------------------
table statement

CREATE TABLE TABELLA1
(id CHAR(5) PRIMARY KEY,
product_name CHAR(20) NOT NULL,
description CHAR(255) NOT NULL,
productor CHAR(50) NOT NULL,
price DECIMAL(7, 2) NOT NULL,
number_of_click INTEGER,
insertion_data DATE NOT NULL);
-----------------------------------------------------
insert statement
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('1', 'Fiat 500 SL', 'etc. etc.', 'FIAT', 3000, '1-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('2', 'Fiat 1500 SQ', 'etc. etc.', 'FIAT', 8200, '10-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('3', 'Lancia Matra', 'etc. etc.', 'Lancia', 6800, '1-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('4', 'Maserati SL', 'etc. etc.', 'Maserati', 13000, '2-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('5', 'Volvo 980', 'etc. etc.', 'Volvo', 9600, '8-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('6', 'Fiat 500 SL', 'etc. etc.', 'FIAT', 9150, '11-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('7', 'Fiat 850 SP', 'etc. etc.', 'FIAT', 7100, '1-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('8', 'Fiat 1500', 'etc. etc.', 'FIAT', 9200, '1-AGO-2003');
--------------------------------------------
I start to try this query:
SELECT top 1 * FROM Tabella1 WHERE id NOT IN (SELECT top 2 * FROM Tabella1
ORDER by id DESC) ORDER BY number_of_click DESC;

and I receive this error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] You have written a subquery that
can return more than one field without using the EXISTS reserved word in the
main query's FROM clause. Revise the SELECT statement of the subquery to
request only one field

What can I have to do
Click to expand...
 
W

WebRaster

I try with
"SELECT top 1 FROM Tabella1 ORDER by number_of_click DESC UNION SELECT TOP 2
FROM Tabella1 ORDER by id DESC;"


BUT I receive this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in ORDER BY clause.
 
W

WebRaster

Sorry I try THIS

SELECT top 1 * FROM Tabella1 ORDER by number_of click DESC UNION SELECT top
2 * FROM Tabella1 ORDER by id DESC

BUT I receive only 2 recordr but I need

the most clicked and the last two records inserted.

Thank in advance


[MVP] S. Clark said:
I think I understand that you need to satisfy three unrelated conditions,
but the SQL you posted doesn't seem to jive with that. So, assuming that
the three needs are independent, then I'll recommend using the UNION
operator.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

WebRaster said:
Hi to everyone, I need an helping hand on this question:

I'm bulding an home page of a cars seller, I need to display in this page
nine photos and descriptions taken by the database, 5 of this are fixed BUT
the other 4 elements had to be choosen in database according these select
criteria: the cheapest, the last two cars inserted in database, and the most
clicked in site. In addiction I have to avoid the accidental repetition of
elements on page, for example the ceapest car could be the last inserted in
database so could be displayed twice.

With the same query I have to extract from database these informations:

1) the cheapest one
2) the last two cars inserted in database
3) most clicked

The database structure in this:
Id (key)
product_name (text)
description (Memo)
price (number)
number_of_click (number)
insertion_data (Date)

My question is: how can I create a query that can work to solve this
problem?
--------------------------------
table statement

CREATE TABLE TABELLA1
(id CHAR(5) PRIMARY KEY,
product_name CHAR(20) NOT NULL,
description CHAR(255) NOT NULL,
productor CHAR(50) NOT NULL,
price DECIMAL(7, 2) NOT NULL,
number_of_click INTEGER,
insertion_data DATE NOT NULL);
-----------------------------------------------------
insert statement
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('1', 'Fiat 500 SL', 'etc. etc.', 'FIAT', 3000, '1-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('2', 'Fiat 1500 SQ', 'etc. etc.', 'FIAT', 8200, '10-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('3', 'Lancia Matra', 'etc. etc.', 'Lancia', 6800, '1-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('4', 'Maserati SL', 'etc. etc.', 'Maserati', 13000, '2-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('5', 'Volvo 980', 'etc. etc.', 'Volvo', 9600, '8-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('6', 'Fiat 500 SL', 'etc. etc.', 'FIAT', 9150, '11-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('7', 'Fiat 850 SP', 'etc. etc.', 'FIAT', 7100, '1-AGO-2003');
INSERT INTO TABELLA1
(id, product_name, description, productor, price, insertion_data)
VALUES
('8', 'Fiat 1500', 'etc. etc.', 'FIAT', 9200, '1-AGO-2003');
--------------------------------------------
I start to try this query:
SELECT top 1 * FROM Tabella1 WHERE id NOT IN (SELECT top 2 * FROM Tabella1
ORDER by id DESC) ORDER BY number_of_click DESC;

and I receive this error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] You have written a subquery that
can return more than one field without using the EXISTS reserved word in the
main query's FROM clause. Revise the SELECT statement of the subquery to
request only one field

What can I have to do
Click to expand...
Click to expand...
 
G

GreySky

WebRaster, I'll try to help you solve it this time : )

You will need to build a few queries here. Some of them
will be used to return a single result (such as the
cheapest one). Then this result will be used to exclude it
as a possible result in others.

I'll try to step you though it, but all of this is air
query (not actually tested).

1) Build a query that returns the cheapest one.

This query actually is two queries. First you must
determine the cheapest price, then you must pick an ID
having that price.

--qryCheapestPrice
SELECT Min([Price]) AS CheapestPrice FROM TABELLA1;

--qryCheapestCar
SELECT First([Id]) As CheapestCarID
FROM TABELLA1 INNER JOIN qryCheapestPrice
ON TABELLA1.Price = qryCheapestPrice.CheapestPrice;

This will yield a Id that points to the cheapest car, and
in case you have two cars with the same cheapest price, it
will just pick the first one.

2) Return the last two cars entered in the database, but
make sure it isn't the cheapest one.

--qryLastTwoEntered
SELECT Top 2 Id As LastTwoEnteredID FROM TABELLA1
WHERE Id Not In (Select [CheapestCarID] FROM
qryCheapestCar)
ORDER BY insertion_data DESC

3) Return the most clicked, but make sure it's not the
cheapest nor last two entered. There's a couple ways of
handling this, and if this is too slow, we can investigate
another possibly faster route.

First, force a subquery to order your entries by the most
clicked. The reason I pick 4 here, instead of 100 percent
(i.e., all of them), is because we are excluding no more
than 3 (leaving at least one).

--qryMostClickedSorted
SELECT TOP 4 Id FROM TABELLA1
ORDER BY number_of_click DESC;

Now use this ordered set to pick the first one, excluding
the ones we've already picked.

--qryMostClicked
SELECT First([Id]) As MostClickedID
FROM qryMostClickedSorted
WHERE Id Not In (Select [CheapestCarID] FROM
qryCheapestCar)
AND Id Not In (Select [LastTwoEnteredID] FROM
qryLastTwoEntered

Now that you have your pieces, you can union them together.

--quniMy4Results
Select CheapestCarID As TheID FROM qryCheapestCar
UNION ALL
Select LastTwoEnteredID As TheID FROM qryLastTwoEntered
UNION ALL
Select MostClickedID As TheID FROM qryMostClicked


Finally, use this union query to return the descriptions
for these IDs.

--qryAboutTime
SELECT TABELLA1.* From quniMy4Results INNER JOIN TABELLA1
ON quinMy4Results.ID = TABELLA1.ID


I hope this helps.

David Atkins, MCP
 
W

WebRaster

Thank you for your interest

But, as you can intend, I'm a newbie so:

Where I've to put "--qryCheapestPrice"?

Perhaps it's a very stupid question so excuse me
 
G

GreySky

No, that's fine; I wasn't clear at all.

When I put "--qryCheapestPrice" I meant for you to give
the query that name (i.e., save the query with the
name "qryCheapestPrice"), as I then use this query in
later queries.

David
 
W

WebRaster

Hi David I try your code but the last query returns this error message :
query too complex
 

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

hard (for me) question on database query 3
Help Please interfacing to SQL 8
Help Building Complex Data Entry Forms – PART 1 3
Installation of Project Portfolio Server fails with SQL Error 5
PLEASE HELP, insert 0
Newbie append querie - Nightmare, please help! 8
Strange query help 4
Help please with Update Query 9

Top