Lazarus

Programming => Databases => Topic started by: Derek2020 on April 08, 2020, 05:45:55 am

Title: Multiple criteria in SQL statement
Post by: Derek2020 on April 08, 2020, 05:45:55 am
Hi
I'm stuck again. I've managed to work around all the other obstacles that I've encountered, with a bit of prompting from kind people here.

I have a master table and the child table, with the child table referencing a column on the master; I know there are several ways to do this but the way I learned is using the following to filter the child table:

Code: [Select]
SQLQueryMaster.Datasource := Master_table;
SQLQueryMaster.SQL.text := 'Select * from Column_Name where TABLE=:Column_Name';


That works for me. But I can't find any way of filtering it based on two tables.

Let's say I managed a car hire company and I had a table of our cars, a table of our customers and a table of previous hires, I can select EITHER the car OR the customer table and get every hire that is connected to one of the subjects, but I can't find a way to use the above code to say "select every hire that has been from this customer in this table and this car in the other table.

As one of the datasources is displayed in a DBLookupComboBox I have also tried referencing the data as:
Code: [Select]
SQLQueryMaster.SQL.text := 'Select * from Column_Name where TABLE=:DBLookupComboBox.Keyfield';But to no avail.


As there's no direct connection between the vehicle and customer databases I can't use a JOIN statement either.

If anyone could point me in the right direction with advice or suggesting a page to look at, I'd be appreciative. Sorry for this being my 3rd question, but the help isn't being wasted I can assure you. 0 to where I am now in a couple of weeks isn't bad going, I think..

Many thanks
Derek



Title: Re: Multiple criteria in SQL statement
Post by: mangakissa on April 08, 2020, 09:02:08 am
Try to use union in your query.

https://www.w3schools.com/sql/sql_union.asp
Title: Re: Multiple criteria in SQL statement
Post by: Derek2020 on April 08, 2020, 12:13:21 pm
Thanks Mangakissa

But as I wrote above, I cannot use a UNION in the SQL as there's no commonality between the two databases.

Thanks anyway

Derek
Title: Re: Multiple criteria in SQL statement
Post by: rvk on April 08, 2020, 12:47:59 pm
As there's no direct connection between the vehicle and customer databases I can't use a JOIN statement either.
But there is a direct connection between vehicle and hire, and hire and customer. So you can use multiple left joins.

"select every hire that has been from this customer in this table and this car "\

Code: SQL  [Select][+][-]
  1. SELECT h.fromdate, h.todate, h.price, c1.name AS customer, c2.name AS carname FROM hire h
  2. LEFT JOIN customer c1 ON c1.id=h.customer_id
  3. LEFT JOIN car c2 ON c2.id=h.car_id
  4. WHERE c2.name = 'maserati' AND c1.name = 'rvk'

(of course the last where line could be changed just to your liking)
Title: Re: Multiple criteria in SQL statement
Post by: Derek2020 on April 08, 2020, 01:50:52 pm
Hello RVK

Thanks for that, it's appreciated. But also my apologies as my example was flawed. I used what I thought was a similar comparison, but it wasn't. I thought my actual project might invite ridicule, but there's no alternative.

It's a database for an on-bus ticket machine.

It has a combo box which displays where the bus is at the moment. A grid below it (one column) to list all the possible places on the route (both of these reference a single list of locations, but use different queries/ datasets) and then there's the fares table to be searched. Basically in English I am trying to put "Search the fares table where departure = the combo box, destination = the grid and then display the fares available.

Apologies, I should have been more forthcoming to start with, but previously when I mentioned this project I ended up spending half the time explaining why I was doing it (a different forum, to be fair).

So I can't see any way to link the two together.

Any ideas, please?
Derek
Title: Re: Multiple criteria in SQL statement
Post by: rvk on April 08, 2020, 02:08:55 pm
Maybe it's better to show the tables and field definitions.

Because now I'm not sure if destination and bus_location are separate tables.
And what are fares? From_location and to_location?

So table definitions and a sketch of what you want would go a long way to an answer.
Title: Re: Multiple criteria in SQL statement
Post by: eljo on April 08, 2020, 02:13:43 pm
You haven't given us any helpful information. What database are you using? what is the schema used? Which sql dialect it uses? have you read https://wiki.freepascal.org/MasterDetail (https://wiki.freepascal.org/MasterDetail) for information and ideas? it looks to me that the bound parameter is most promising.
Title: Re: Multiple criteria in SQL statement
Post by: Derek2020 on April 08, 2020, 02:40:03 pm
Hello RVK

The LOCATION table has just two fields-   Location_ID (autoincr) and Location_Name (string)
The location table is used both for the DBLookupcombobox and the single column grid below it, but using 2 different queries and datasources- otherwise any change to one would automatically change the other, as I found out.

The table to be looked up is named FARES and has Fare_ID (autoincr), Departures (string), Destination (string) and Fare (number).

The aim is to get FARES.Departure = DBLookupcombobox. value and FARES.Destination = grid.value (written in plain English, I know that's not the correct syntax).

I've tried other things such as using 2 separate TEdit boxes to provide the information to filter and I have had some limited success, but it always needs the refresh button on the navigator pressed first. (for that I used the following. It still doesn't work properly and when I've tried to replace edit3 and edit 4 with the combo box/ grid it doesn't want to know.

Code: [Select]
   Sqlquery4.sql.text := 'Select * from fares where departure = :DEPPOINT and destination = :ARRPOINT';
  Sqlquery4.params.parambyname('DEPPOINT').Asstring := Edit3.text;
  Sqlquery4.params.parambyname('ARRPOINT').Asstring := Edit4.text;   

Again, I thank you for any advice.
Derek
Title: Re: Multiple criteria in SQL statement
Post by: Derek2020 on April 08, 2020, 02:42:27 pm
Hello Eljo

I am using SQLite. I don't know what you mean by 'which schema', sorry. Yes, I have seen something similar to the master detail example you suggested. As I put in the OP I've tried that but it will only work for one master and one child table.

I've followed the link to 'bound parameter' but I have to say I'm not really sure what it's telling me. A lot of the help on that site goes from the ultra basics to suddenly the highly complex, with the bit in the middle missed out.

Thanks
Derek
Title: Re: Multiple criteria in SQL statement
Post by: rvk on April 08, 2020, 10:34:46 pm
Wouldn't this:
Select * from fares where departure = :DEPPOINT and destination = :ARRPOINT
not just result in one fare?

What is the location table for if you already have strings in the fares table for departure and destination?
Normally you would have a location table with location_id and then a fares table with departure_location_id and destination_location_id. In that case when you change the name of one location you won't have to do it in multiple tables. But as you said you have a string as departure and destination in the fares table, you didn't opt for that option. But what is the location-table for then?

You can just have a TDBLookupBox with one query (select departure from fares)
and a DBGrid underneath with another query connected to the TDBLookupBox (select destination from fares where departure=:departure)

The second :departure is taken from the first query.

Otherwise you need to provide more details and complete Schema. This is the CREATE TABLE with field definitions like you need to create a new database.

You also mentioned "route". Is that something different from a fare? Or do you have multiple fares records with the same fare_id which results in a route. In that case... how do you distinguish the order of the destinations? There are a lot of details you still didn't mention.
Title: Re: Multiple criteria in SQL statement
Post by: Derek2020 on April 08, 2020, 11:34:15 pm
Hello RVK

The code 'Select * from fares where departure = :DEPPOINT and destination = :ARRPOINT' would result in just one fare, yes. That's what I want (I can use additional fields in the fare table for adult, child etc but that's complicating things for now).

I could do it as you suggest with a single table in the BDGrid, but that would make it harder to maintain the database in the future, I think.

The route table is just which bus route it is, however, the list of locations in the combo and grid are filtered to only show locations relevant to that route. If I take your suggestion then I'm still in the same position of having to filter the table based on the data in two existing combo boxes.


There has to be a way for me to select data from table C where it matches table A and table B, I just can't figure out how.



Thanks
Derek
Title: Re: Multiple criteria in SQL statement
Post by: rvk on April 08, 2020, 11:41:00 pm
There has to be a way for me to select data from table C where it matches table A and table B, I just can't figure out how.
As of now you only explained one table. Fares (probably C)
So what are tabe A and B?

Can you again explain in detail what exactly you have in terms of tables and what you want out of them. Becayse you haven't described the route table at all (and what you want with it).

Communicating and describing your desired results is a big part of programming too.

Title: Re: Multiple criteria in SQL statement
Post by: Derek2020 on April 09, 2020, 12:04:12 am
There are three tables

ROUTE
Route_ID (autoinc)
Route_Name (string)

LOCATION
Location_ID (autoinc)
Location_Name (string)

FARES
Fare_ID (autoinc)
Fare_Departure (string)
Fare_Destination (String)
Fare_Value (number)


The form is set up with a DBLookupComboBox at the top, which selects the route.
There is another DBLookupComboBox beneath it, which selects all locations encountered on that route.
Then there's a DBGrid below it which again selects all locations on that route.

Both the location boxes (combo and grid) take their data from the same location table, but use a separate query/ dataset.

Using SQL I can select ONE filter for the fares- (Select * from fares where fare_destination =:Location_Name) using the master/slave database example shown already. But I can't find any way to achieve the same using two queries.


I'm sorry that my intent isn't clear enough. As in any subject knowing how to ask the questions, and what to ask is just as important as anything else. Your help is appreciated, but I know it must be irritating for you.

Thanks
Derek
Title: Re: Multiple criteria in SQL statement
Post by: eljo on April 09, 2020, 03:22:40 am
multiple ways to address the problem
1) make sure that the destination location dataset that is shown in the grid returns at least the following fields
  1) route id
  2) location ID
  3) location name.
  You can retreive the routeID either by using an inner join or by using a calculated field oon the fare locations dataset. After that add the parameters as described by avk and now since both key fields are in one dataset it should be easier to link them.

2) forgo all master detail automatic linking between datasets and use the on after scroll event of the locations dataset to close the fare dataset set the new parameters and reopen it manually.

EDIT: some typos and wrong dataset names hopefully it wasn't to confusing.
Title: Re: Multiple criteria in SQL statement
Post by: mangakissa on April 09, 2020, 09:51:19 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?
Title: Re: Multiple criteria in SQL statement
Post by: Derek2020 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

Title: Re: Multiple criteria in SQL statement
Post by: rvk 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.
Title: Re: Multiple criteria in SQL statement
Post by: Derek2020 on April 09, 2020, 05:51:44 pm
Hi

If this helps, this is the way I've designed the interface.
Title: Re: Multiple criteria in SQL statement
Post by: Derek2020 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
Title: Re: Multiple criteria in SQL statement
Post by: eljo 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.

Title: Re: Multiple criteria in SQL statement
Post by: egsuh 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.
Title: Re: Multiple criteria in SQL statement
Post by: Derek2020 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
Title: Re: Multiple criteria in SQL statement
Post by: egsuh 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.       

Title: Re: Multiple criteria in SQL statement
Post by: Derek2020 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
Title: Re: Multiple criteria in SQL statement
Post by: Derek2020 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
Title: Re: Multiple criteria in SQL statement
Post by: egsuh 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.
Title: Re: Multiple criteria in SQL statement
Post by: Zvoni 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 €
TinyPortal © 2005-2018