Hi all,
in short here it is my problem:
I have to monitor the prices of a product sold by different shops and I need
a query to retrieve a list of shops with the associated CURRENT price of the
product.
The structure is modelled by 2 tables in a 1 to n relation:
SHOPS
id counter
name text
address text
&
PRICES
id counter
price value
date text
idShop numeric
How can I obtain a result of this kind:
idShop name address price date
with all recorded shops showing the most recent date and price?
I was only able to retrieve the current date with the query:
SELECT B.IDSHOP,MAX(B. DATE) AS LU, A.NAME, A.ADDRESS FROM PRICES B LEFT
OUTER JOIN SHOPS A ON B.IDSHOP=A.ID GROUP BY IDSHOP, A.NAME, A.ADDRESS
but if I try to add to the select the price, the group by clause gives
(correctly) a result not suitable for my aims.
I also tried with subqueries but I had the same results as above.
Any hint?
TIA
Claudio 1 1295
"Claudio Magno" <ma************ @libero.it> wrote in message
news:43******** **************@ reader2.news.ti n.it... Hi all, in short here it is my problem: I have to monitor the prices of a product sold by different shops and I need a query to retrieve a list of shops with the associated CURRENT price of the product. The structure is modelled by 2 tables in a 1 to n relation:
SHOPS id counter name text address text
&
PRICES id counter price value date text idShop numeric
How can I obtain a result of this kind: idShop name address price date with all recorded shops showing the most recent date and price?
I was only able to retrieve the current date with the query: SELECT B.IDSHOP,MAX(B. DATE) AS LU, A.NAME, A.ADDRESS FROM PRICES B LEFT OUTER JOIN SHOPS A ON B.IDSHOP=A.ID GROUP BY IDSHOP, A.NAME, A.ADDRESS but if I try to add to the select the price, the group by clause gives (correctly) a result not suitable for my aims. I also tried with subqueries but I had the same results as above. Any hint? TIA Claudio
Your structure hints that each shop only sells one product - I guess this is
OK. However, you should not use 'name' and 'date' for field names - they
are reserved words and this can cause you trouble. Change them to
shop_name, price_date, or whatever.
The other point to check is the join. Do you need:
Only shops with at least one price record
All shops regardless of whether they have any price records
A list of all the latest prices regardless of if there is a matching shop
(should not be the case if the database is set up correctly)
The following should give you option 1:
SELECT s.id, s.name, s.address, p.price, p.date
FROM SHOPS AS s INNER JOIN PRICES AS p
ON s.id=p.idshop
WHERE p.id IN
(SELECT TOP 1 id FROM PRICES
WHERE prices.idshop=s .id
ORDER BY PRICES.date DESC, PRICES.id) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: majsen |
last post by:
Hi, I have problem running this query. It will time out for me...
My database are small just about 200 members.
I have a site for swaping appartments (rental). my query should look
for match
in a triangle. Like this member A -> B->C
A give his appartment to B. B gives his appartment to C and finally
C gives his appartment to A
Soo my query looks for matching parameters like rooms, location, size
|
by: AMD Desktop |
last post by:
Here is the table:
CREATE TABLE (
IDENTITY (1, 1) NOT NULL ,
NOT NULL ,
NOT NULL ,
NOT NULL CONSTRAINT
DEFAULT (0),
NOT NULL ,
NOT NULL
) ON
|
by: stu_gots |
last post by:
I have been losing sleep over this puzzle, and I'm convinced my train
of thought is heading in the wrong direction. It is difficult to
explain my circumstances, so I will present an identical make-believe
challenge in order to avoid confusing the issue further.
Suppose I was hosting a dinner and I wanted to invite exactly 12 guests
from my neighborhood. I'm really picky about that... I have 12 chairs
besides my own, and I want them all...
|
by: rsarath |
last post by:
Hello,
I have the following setup and I would appreciate any help in improving
the performance of the query.
BigTable:
Column1 (indexed)
Column2 (indexed)
Column3 (no index)
Column4 (no index)
|
by: |
last post by:
having a spot of trouble writing this one. if you are so inclined and have a
moment, i'd really appreciate your insight.
i have what amounts to a purchase order type of setup...a descriptive header
associated with line items. i have 3 tables...header, lineitem, and
auxiliary. the auxiliary table holds information like subbordinate
information about lineitems that are "non-standard". each of these tables
has a uniqueId column. the...
| |
by: Xu, Wei |
last post by:
Hi,
I have wrote the following sql sentence.Do you have comments to improve
the performance.I have created all the indexed. But it's still very
slow.Thanks
The primary key is proj_ID and Task_UID.
SELECT PR.PROJ_NAME AS PRName, PR.PROJ_ID As PRProjID, PR.TASK_UID As
PRTaskUID, 'Dev' AS GroupType,
Feat.PROJ_ID As FeatProjID, Feat.TASK_UID As FeatTaskUID, Feat.FeatureID AS
FeatureID,
|
by: schoultzy |
last post by:
Hello Everyone,
This is probably a simple fix so be kind when/if you reply. The query
below retrieves information for individuals based on a column named
ATTRIB_DEF, and assorted other columns; however, the ATTRIB_DEF column
is important one. Currently the query gets all individuals that have
an ATTRIB_DEF that contains the string 'AC1' at the beginning of the
entry. I want the query to do the opposite. I want all of the
individuals...
|
by: yanjie.ma |
last post by:
Hi,
I've got a two part question on table and form design (sorry for the
length but it takes a bit to explain). Our sales department uses a
look-up table to help the them select the best equipment to use for a
customer's needs. It basically consists of a table with the following
setup:
Equipment: Option1: Option2: Option3: Option 4: ...
-------- ------- ------- -------- --------
|
by: n |
last post by:
Hello! Here is a problem I hope you can point me to a solution. It
Problem: A teacher needs to know which lesson to teach.
A school has a curriculum with 26 lessons, A-Z.
For a given class, a random number of students arrive, each of which
has completed a random number of lessons taken at random from the
curriculum.
|
by: Justin |
last post by:
Here's my XML:
<?xml version="1.0" ?>
<AppMode Type="Network">
<CurrentFolder Path="c:\tabs">
<Tabs>
<FilePath>tabs\Justin.tab</FilePath>
<FilePath>tabs\Julie.tab</FilePath>
*****There could be 1 of these or 100....quantity can change*****
</Tabs>
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
| |
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |