Recent

Author Topic: Multiple criteria in SQL statement  (Read 3803 times)

Derek2020

  • New Member
  • *
  • Posts: 25
Re: Multiple criteria in SQL statement
« Reply #15 on: April 09, 2020, 05:27:47 pm »
Thanks all.

Eljo, I'm really sorry but I don't understand what you mean by that. I'm familiar with joining tables in SQL statements as I used that in MS Access.

If I understand rightly, you mean leave the DBLookupComboBox 'as is' and then the TBGrid underneath it uses a SQL string joining the route table and fares table below it? That would work if there were only one or two routes, but if there were several (let's just say there are multiple routes all running into a City, I'd have to duplicate the fares for each and every route between two points).

I can perform a search based on "select any record where column X equals the contents of editbox1" (written in English!) and I dare say I could apply two criteria by using two editboxes... yet I can't use something similar to query based on the visible string in a lookup combo box- or at least I haven't found it yet.

Can anyone recommend any good books on the subject? Something as simple as this should be easier to implement and I cannot keep bothering you good people.

Very frustrating, but thanks.

Derek


rvk

  • Hero Member
  • *****
  • Posts: 6112
Re: Multiple criteria in SQL statement
« Reply #16 on: April 09, 2020, 05:39:06 pm »
(let's just say there are multiple routes all running into a City, I'd have to duplicate the fares for each and every route between two points).
But you still haven't explained yet how these tables connect to each other. You say you have a route table but I only see a name in that. Not a destination or departure.

Can you provide some real sample data which would make it all a lot clearer.

Derek2020

  • New Member
  • *
  • Posts: 25
Re: Multiple criteria in SQL statement
« Reply #17 on: April 09, 2020, 05:51:44 pm »
Hi

If this helps, this is the way I've designed the interface.

Derek2020

  • New Member
  • *
  • Posts: 25
Re: Multiple criteria in SQL statement
« Reply #18 on: April 09, 2020, 05:52:36 pm »
and this is the way the database works- or is supposed to. This is the way I'd do it in MS Access and obviously it's not done in the same way here.

Thanks
Derek

eljo

  • Sr. Member
  • ****
  • Posts: 468
Re: Multiple criteria in SQL statement
« Reply #19 on: April 09, 2020, 06:11:57 pm »
Thanks all.

Eljo, I'm really sorry but I don't understand what you mean by that. I'm familiar with joining tables in SQL statements as I used that in MS Access.
by what I gave two different strategies please be specific.
If I understand rightly, you mean leave the DBLookupComboBox 'as is' and then the TBGrid underneath it uses a SQL string joining the route table and fares table below it?
No, the opposite. Joining the route table with dDBLookupComboBox above to create a single dataset with all the keys you need for the fares datasets.
That would work if there were only one or two routes, but if there were several (let's just say there are multiple routes all running into a City, I'd have to duplicate the fares for each and every route between two points).
I have no idea what you are objecting about, to me it looks like that longer routes should be more expensive than shorter routes on the same finish then again I haven't touched that subject at all.

I can perform a search based on "select any record where column X equals the contents of editbox1" (written in English!) and I dare say I could apply two criteria by using two editboxes... yet I can't use something similar to query based on the visible string in a lookup combo box- or at least I haven't found it yet.
em what that has to do with anything? are you telling me that there is a link between the route name and some other field in the fares table?
Can anyone recommend any good books on the subject? Something as simple as this should be easier to implement and I cannot keep bothering you good people.
Which subject? relational databases, sql language, data access library of fpc (SQLDB) something I missed? maybe it would be easier to create a small demo application with a demo database to show how your form works at the moment and a small text demonstrating what you need to remove or add, in which control of your form. I guess solving the specific case will be more beneficial to you at the moment instead of the generic guides to general directions of development.

In any case here a generic skeleton of what you strugling with.

Think of a database application as a 3 layered cake. First layer the database where everything collects. Second layer the data in your application's memory. the datasets. Third and last layer the components on your forms, which is the current way of interacting with the data nothing more.
What I'm talking in both my cases in my previous post is to change the second layer, the data in the datasets.

The datasets have two modes of operation dynamic and static schema (in the database world schema refers to the shape your database has, its tables, fields, types, sizes, stored procedures, triggers etc).

If you are using a dynamic schema you are free to change the fields that are returned by changing the select sql.
In static schema dataset, it has a list of field references defined at design time to which it gives you access to, those fields must always exist in your select sql that you are using.

if you choose to use a static schema then you can add fields that are calculated at runtime, by code you attach to oncalculate event (if I remember correctly) that can return any value you desire based on any number of parameters or rules, including the currently selected keyvalue of your dblookupCombobox. 

By adding that key value in the in memory dataset of locations (and not changing the underlying table) you pretend that the master key on your master dataset is a composite key(which means a key that is based on more than one fields) and as such you are short circuit your design to accomplish your goal with out huge changes in the underline database or second layer of your application.

Good luck for a speedy resolution I will not have access to the forums for the next day or two.

« Last Edit: April 09, 2020, 06:16:56 pm by eljo »

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: Multiple criteria in SQL statement
« Reply #20 on: April 10, 2020, 11:12:11 am »
Before designing the interface, please explain more about the logic. 

You have routes, locations, and fares.  (forget about field names and IDs).

I can think of Locations table. Each record has one location, like London, York, New Castle, etc.
And fares table as well --- it contains departure, destination, one-way fare, and return fare.  But one question --- it is possible that the fares between the same cities are different when the departure and destination are swapped? For example, may fare of London -> York be different from York -> London fare?

Regarding Routes, I'm not clear. Do you have only one route, e.g. London - York - New Castle, or could there be many routes like :

Route 1  :  London - York - New Castle
Route 2  :  Brighton - London - Birmingham - Manchester
Route 3  :  Leiceister - Bristol - Cardiff - Swansea

and so on.  Let's assume many routes.

In this case, if I select London as departure, then 5 cities (York, New Castle, Brighton, Birmingham, and  Manchester) must pop up for destination to choose, and when the destination is selected, then fares will be displayed.

Or is it possible to choose any combination of cities?

Please confirm whether this is what you want first. Then let's think about next step.

Derek2020

  • New Member
  • *
  • Posts: 25
Re: Multiple criteria in SQL statement
« Reply #21 on: April 10, 2020, 01:13:08 pm »
Hello Egsuh, thanks your your message.

"In this case, if I select London as departure, then 5 cities (York, New Castle, Brighton, Birmingham, and  Manchester) must pop up for destination to choose, and when the destination is selected, then fares will be displayed."

Yes, that's it.

I've just started the coding again from scratch (same DB) and got a little further this time.

Code: Pascal  [Select][+][-]
  1. SQLQuery1.SQL.text := 'Select * from fares where departure = :Departure and destination = :Destination';
  2. SQLQuery1.Params.Parambyname('departure').AsString := Combodeparture.text;
  3. SQLQuery1.Params.Parambyname('destination').AsString := ListboxDestination.keyvalue;  
  4.  

That allowed me to achieve the fares look up I wanted- I've no idea why it wasn't working before on the original attempt. However, that was without any reference to the route, so every location option was available, regardless of the planned route. I used a master-child relationship to only display the location data for the route selected and now the fares are showing blank. I think I need to explore the SQL union in more detail or perhaps the bound function. I don't know if that helps me or hinders.

Thanks again for your reply. I'm pleased that someone has understood what I'm trying to do.

Derek

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: Multiple criteria in SQL statement
« Reply #22 on: April 10, 2020, 01:55:49 pm »
If you want only fares, then what you need are only one table --- fares table, which contains both of e.g. London -> York and York -> London.

First, you create a departure dataset, using  "select distinct(departure) from fares" and then set ListField and KeyField of DBLookUpComboBox to the Departure field  (Leaving datasource and datafield properties of the DBLookUpComboBox blank).

Once any citi is chosen, then you should fill the Destination Listbox with something like

     select destination from fares where departure=:dept
      ParamByName('dept').AsString := DBLookupComboBox.Caption;

    probably in the DBLookUpComboBox's OnSelection(?) event handler. Actually the event handler would look like : 

Code: Pascal  [Select][+][-]
  1.             with  SQLQuery2 do begin
  2.                 Close;
  3.                 ParamByName('Dept').AsString := DBLookUpComboBox.Caption;
  4.                 Open;
  5.              end;
  6.  
           


And when the destination is chosen, similar query again.

     select fare_type, fare from fares where departure=:dept and destination = :dest;
     // params := ....

     Opening this SQLQuery will show the fares.


But the route should be there for displaying departure time, etc. So there should be another table for time, etc.  Sorry that the explanation is not detailed enough. Hope you catch the ideas.       


Derek2020

  • New Member
  • *
  • Posts: 25
Re: Multiple criteria in SQL statement
« Reply #23 on: April 10, 2020, 02:33:55 pm »
Thanks Egsuh,

That's given me a lot to think on there. I still have to filter the locations by only the selected route, but I think you've given me enough info there for me to work through, try it, experiment with modifications etc.

I really appreciate it, your response was crystal clear. Just from reading it on screen, I can see it has answered at least two points I was unsure on.

Thanks
Derek

Derek2020

  • New Member
  • *
  • Posts: 25
Re: Multiple criteria in SQL statement
« Reply #24 on: April 10, 2020, 05:33:08 pm »
UPDATE

Thanks Egsuh (and others). I have re-done it all again and now it's working well. I have incorporated some of the changes you suggested. Ironically, this is now similar to how I originally envisaged the code- I have no idea why it wouldn't work earlier, but presumably some sort of clash or error somewhere.

One minor issue I'm still finding is that after filtering the data (I have this acting on 'onclick' I then have to press the 'refresh' button on the navigator panel; is there a way to enter some code to automatically refresh it without the use of the navigator panel, please? Update: I've just found how to do that too. SQLQuery1.Refresh;

Again my thanks. I've been stumped on this for days. The rest of the project all seems quite straight forward now.

Derek
« Last Edit: April 10, 2020, 05:44:42 pm by Derek2020 »

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: Multiple criteria in SQL statement
« Reply #25 on: April 11, 2020, 06:55:08 am »
To carry coals to New Castle,

You may use TSQLite3DataSet instead of TSQLQuery if your DB is SQLite3 (you don't need SQLite3Connection nor TSQLConnector).

You can assign a table of SQLite (e.g. Cities table) to TSQLite3DataSet by setting tablename directly, without using SQL query.
Within the SQLite3DataSets, you can define master-slave relationship using properties of MasterSource, MasterFields, and IndexFieldNames.

Let's assume that you have two tables, cities (which includes all cities, having CITI field) and routes (which has departure and all destination cities from the departure -- field names would be DEPARTURE and DESTINATION) . 

You assign table cities to DataSet1, and routes to DataSet2 (DataSet1, DataSet2: TSQLite3DataSet).
And set properties as followings. 

      DataSource1.DataSet = DataSet1
      DataSource2.DataSet = DataSet2

      DataSet2.MasterSource = DataSource1
      Dataset2.MasterFields = City
      DataSet2.IndexFieldNames = Departure

And link DBGrids to dataset1 and dataset2. If you open both of dataset1 and dataset2, and move around DataSet1, you will be able to see how the destinations change.

To display fares, you may need another table Fares. Here, you may try DataSet3.MasterFields = DEPARTURE;DESTINATION (This should work but I have never done. Notice that property name is MasterFields, not MasterField).  Or you may find workaround with only one table, and mixing SQLQueries.

If the tables are not too large, accessing tables directly do not need much codes for yourself to write.

These are the features of TTable of Delphi, but Lazarus does not provide standard TDataSet descendant that has the same feature. TSQLite3DataSet does.

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Multiple criteria in SQL statement
« Reply #26 on: April 27, 2020, 09:50:34 am »
If this the three tables you talk about there's no relation at all.
Quote
There is another DBLookupComboBox beneath it, which selects all locations encountered on that route.
How is this possible when there's no relation to table ROUTE?
Correct.
1) There is a Table missing connecting Routes and Locations in a "m:m"-Relation (A location (=Busstop?) can be on multiple Routes (=Switchover)).
2) Usually the locations on a route are in a specific order
3a) In your Fares-table there is a connection to the route missing
3b) Why do you use Strings for Departure and Destination-Locations, if you have a designated Locations-Table?
4) Have you considered the "Zone"-Problem? e.g. Zone1 (=Downtown)=2,50 € / Ticket, Zone2 (Suburbs) = 2,80 €
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018