Jet SQL
Jet SQL
Jet SQL
Jet SQL
Jet SQL
Jet SQL Jet SQL Jet SQL Jet SQL Jet SQL Jet SQL Jet SQL Jet SQL
Jet SQL Jet SQL
Jet SQL
Go Back  Xtreme Visual Basic Talk > > > Jet SQL


Reply
 
Thread Tools Display Modes
  #1  
Old 07-14-2017, 06:25 AM
Daigon Ali Daigon Ali is offline
Freshman
 
Join Date: Aug 2014
Location: London
Posts: 43
Default Jet SQL


Hi,

I'm using Jet SQL to query an Access mdb database.
There are two tables, SpeciesTable and ValuesTable both containing around 100,000 records.
SpeciesTable contains botanical species. It doesn't contain any duplicates.
ValuesTable contains the species plus numerical valueS.

What I'm trying to do is to join the tables so that I have the species plus the max numerical value.

E.g.
SpeciesTable:
Species
Oak
Maple

ValuesTable:
Species----Value
Oak---------- 6
Oak-----------2
Oak-----------9
Oak-----------4
Oak-----------5
Maple---------5
Maple---------7
Maple---------6
Maple---------4

So the query should return:
Oak -----9
Maple--- 7


So far I've come up with this. It doesn't throw any errors but the application becomes permanently unresponsive.
Code:
SELECT * FROM SpeciesTable AS a LEFT JOIN (SELECT Species, MAX(Value) FROM ValuesTable GROUP BY Species) AS b ON a.Species = b.Species
Reply With Quote
  #2  
Old 07-14-2017, 07:46 AM
Daigon Ali Daigon Ali is offline
Freshman
 
Join Date: Aug 2014
Location: London
Posts: 43
Default

I found the answer which was to discount null values.

So
Code:
SELECT * FROM SpeciesTable AS a LEFT JOIN (SELECT Species, MAX(Value) FROM ValuesTable WHERE Value IS NOT NULL GROUP BY Species) AS b ON a.Species = b.Species
Reply With Quote
Reply

Tags
species, valuestable, speciestable, select, oak, join, numerical, jet, query, sql, tables, b.species, maple---------5, maple---, maple---------4, maple---------6, return, maple---------7, maxvalue, permanently, left, unresponsive, oak-----------5, application, errors


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Jet SQL
Jet SQL
Jet SQL Jet SQL
Jet SQL
Jet SQL
Jet SQL Jet SQL Jet SQL Jet SQL Jet SQL Jet SQL Jet SQL
Jet SQL
Jet SQL
 
Jet SQL
Jet SQL
 
-->