DB + Grid design question

[WindForce]

Hi. DB is mySql.
I have a parent table, let's call it TravelServices, with the following data:

ID  ServiceType SupplierID
1      Hotel           3
2      Car             2
3      Guide           3

Then an Hotels table (HotelID, HotelName), a CarSuppliers table (CarSupplierID, CarSupplierName) and a Guides table (GuidID, GuideName)

I would like to build a grid for the TravelServices, like so:

ID   ServiceType  SupplierID  SupplierName
1      Hotel                3   Intercontinental Hotel
2      Car                  2   Acme Car Supplier
3      Guide                3  Joe the great guide

How do i create this grid?

Thanks
Jaime

[Peter.Greulich]

Re: DB + Grid design question

Jamie,

I would simply use a SQL view.

[WindForce]

Re: DB + Grid design question

Could someone please help me with the SELECT statement?

[JimDK]

Re: DB + Grid design question

When you specify your table in the grid definition, use the SQL Query builder, there you can select your tables, and create the join.

Builder-1.jpgBuilder-2.jpg

[WindForce]

Re: DB + Grid design question

Hi Jim, thanks for helping.
I know how to attach the queries to the grids or dialogs. The problem is that in this case, i need to do a UNION query, so i will check if i can manually enter a UNION query as the db for my grid, otherwise i'll have to use a view, as Peter proposed...

Jaime

[agillbb]

Re: DB + Grid design question

How you do the UNION query depends on the SQL DB.

In general, I would create a union query for lookup purposes only (in the DB itself), like:

SELECT 'Hotel' AS ServiceType, HotelID AS SupplierID, HotelName AS SupplierName FROM Hotels
UNION
SELECT 'Car' AS ServiceType, CarID AS SupplierID, CarName AS SupplierName FROM Cars
UNION
SELECT 'Guides' AS ServiceType, GuideID AS SupplierID, GuideName AS SupplierName FROM Guides

Then I would create a VIEW for use in the grid. The view should be in the DB itself as well. The view could be defined like:

SELECT ts.ID, ts.ServiceType, ts.SupplierID, muq.SupplierName
FROM TravelServices AS ts, MyUnionQuery AS muq
WHERE muq.ServiceType=ts.ServiceType AND muq.SupplierID=ts.SupplierID
ORDER BY ts.ID

Then, just refer to this view in your grid.

ANdy.

[agillbb]

Re: DB + Grid design question

Note that you could probably achieve this within the A5 query builder, but I personally think it is cleaner and better to keep this type of complexity out of A5 - the DB environment is better suited to handle it.

[WindForce]

Re: DB + Grid design question

Agree with you Andy. Thanks for helping

Jaime

[KYBiggs]

Re: DB + Grid design question

How you do the UNION query depends on the SQL DB.

In general, I would create a union query for lookup purposes only (in the DB itself), like:

SELECT 'Hotel' AS ServiceType, HotelID AS SupplierID, HotelName AS SupplierName FROM Hotels
UNION
SELECT 'Car' AS ServiceType, CarID AS SupplierID, CarName AS SupplierName FROM Cars
UNION
SELECT 'Guides' AS ServiceType, GuideID AS SupplierID, GuideName AS SupplierName FROM Guides

Then I would create a VIEW for use in the grid. The view should be in the DB itself as well. The view could be defined like:

SELECT ts.ID, ts.ServiceType, ts.SupplierID, muq.SupplierName
FROM TravelServices AS ts, MyUnionQuery AS muq
WHERE muq.ServiceType=ts.ServiceType AND muq.SupplierID=ts.SupplierID
ORDER BY ts.ID

Then, just refer to this view in your grid.

ANdy.

That is interesting - In A5 SQL query I can build a Union query but in MS Access the same query saved which is now a view in A5 does not show up as a table/view.

ie The union query above called qrtCarHotelGuides would not show up in A5 but if I build it in SQL Query genie it works?

[WindForce]

Re: DB + Grid design question

Hi Ken
I have never used Access, but this may be related to the UNION syntax in access. When you do it in A5, where PortableSQL is enabled by default, your queries are 'translated' to your backend's syntax. Try turning PortableSQL off in your grid, does the query still work?

Jaime

[agillbb]

Re: DB + Grid design question

In Access, you need to create a new query and place your UNION query (and selected fields) into this. Then A5 will see the new query as a standard view.

[KYBiggs]

Re: DB + Grid design question

Hi Ken
I have never used Access, but this may be related to the UNION syntax in access. When you do it in A5, where PortableSQL is enabled by default, your queries are 'translated' to your backend's syntax. Try turning PortableSQL off in your grid, does the query still work?

Jaime

I looked for the setting in Properties and looked in XBasic setting - "PortableSQL"
I did not see it.
Where would I go to change it?