Recent

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

Derek2020

  • New Member
  • *
  • Posts: 25
Multiple criteria in SQL statement
« 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



« Last Edit: April 08, 2020, 05:48:47 am by Derek2020 »

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Multiple criteria in SQL statement
« Reply #1 on: April 08, 2020, 09:02:08 am »
Try to use union in your query.

https://www.w3schools.com/sql/sql_union.asp
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

Derek2020

  • New Member
  • *
  • Posts: 25
Re: Multiple criteria in SQL statement
« Reply #2 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

rvk

  • Hero Member
  • *****
  • Posts: 6056
Re: Multiple criteria in SQL statement
« Reply #3 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)
« Last Edit: April 08, 2020, 12:50:17 pm by rvk »

Derek2020

  • New Member
  • *
  • Posts: 25
Re: Multiple criteria in SQL statement
« Reply #4 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

rvk

  • Hero Member
  • *****
  • Posts: 6056
Re: Multiple criteria in SQL statement
« Reply #5 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.

eljo

  • Sr. Member
  • ****
  • Posts: 468
Re: Multiple criteria in SQL statement
« Reply #6 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 for information and ideas? it looks to me that the bound parameter is most promising.

Derek2020

  • New Member
  • *
  • Posts: 25
Re: Multiple criteria in SQL statement
« Reply #7 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

Derek2020

  • New Member
  • *
  • Posts: 25
Re: Multiple criteria in SQL statement
« Reply #8 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

rvk

  • Hero Member
  • *****
  • Posts: 6056
Re: Multiple criteria in SQL statement
« Reply #9 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.
« Last Edit: April 08, 2020, 10:37:51 pm by rvk »

Derek2020

  • New Member
  • *
  • Posts: 25
Re: Multiple criteria in SQL statement
« Reply #10 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

rvk

  • Hero Member
  • *****
  • Posts: 6056
Re: Multiple criteria in SQL statement
« Reply #11 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.


Derek2020

  • New Member
  • *
  • Posts: 25
Re: Multiple criteria in SQL statement
« Reply #12 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

eljo

  • Sr. Member
  • ****
  • Posts: 468
Re: Multiple criteria in SQL statement
« Reply #13 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.
« Last Edit: April 09, 2020, 08:04:31 am by eljo »

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Multiple criteria in SQL statement
« Reply #14 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?
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

 

TinyPortal © 2005-2018