Two tables DB and difficult Queries

[kirsch]

EXAMPLE DESCRIPTION

The DB can be seen as a Flower Shop situation using only 2 tables :

1. T_bouquets, main table which records contain
   a) a name bouquet_Name
   b) six flower fields ID_flowerN (N from 0 to 5) containing
   an optional reference to a flower in T_flower_list.

2. T_flower_list , contains the names flower_name of available flowers.

So each main table record defines a bouquet’s name and up to 6 references to the selected flowers. And one can imagine all kinds of bouquets : undefined bouquet with no flower, some bouquets might have only one type of flower, other bouquets could combine flowers randomly, etc…

I have created a few queries displaying the ID-flower references in T_bouquets (with the exception of ID_flower0 which can be displayed by name through the relation). It works, but the reference numbers in the query results have no meaning to the end user florist, who wants to read the flower names!

HELP NEEDED

HOW TO establish the needed relations between the two tables and set up the SQL to display the flower names in place of the flower reference ID number ? It needs to change my “OK_by_ID…” queries In the example to some new “OK_BY-NAME…” queries ! I believe that it is possible in LibO.

NOTE :

Maybe that he solution needs a different table approach, or it might have something to do with the AskLibO discussion here Grouping in base report based on multiple fields with common entries ?

EXAMPLE DOWNLOAD

http://r.kirsch.free.fr/LibO/FlowerShop.odb

[JohnSUN]

I think that will suit SQL-query like this:

SELECT "T_bouquets"."id_bouquet"
  , "T_bouquets"."bouquet_Name"
  , "T_flower_list"."flower_name" AS "flower_name1"
  , "T_flower_list_1"."flower_name" AS "flower_name2"
  , "T_flower_list_2"."flower_name" AS "flower_name3"
  , "T_flower_list_3"."flower_name" AS "flower_name4"
  , "T_flower_list_4"."flower_name" AS "flower_name5"
  , "T_flower_list_5"."flower_name" AS "flower_name6"
FROM "T_bouquets" 
  LEFT OUTER JOIN "T_flower_list" AS "T_flower_list_1"
    ON "T_bouquets"."ID_flower1" = "T_flower_list_1"."ID_flower" 
  LEFT OUTER JOIN "T_flower_list" AS "T_flower_list_2"
    ON "T_bouquets"."ID_flower2" = "T_flower_list_2"."ID_flower" 
  LEFT OUTER JOIN "T_flower_list" AS "T_flower_list_3"
    ON "T_bouquets"."ID_flower3" = "T_flower_list_3"."ID_flower"
  LEFT OUTER JOIN "T_flower_list" AS "T_flower_list_4"
    ON "T_bouquets"."ID_flower4" = "T_flower_list_4"."ID_flower"
  LEFT OUTER JOIN "T_flower_list" AS "T_flower_list_5"
    ON "T_bouquets"."ID_flower5" = "T_flower_list_5"."ID_flower"
  LEFT OUTER JOIN "T_flower_list"
    ON "T_bouquets"."ID_flower0" = "T_flower_list"."ID_flower"

[kirsch]

Hi John
Thanks a lot for your SQL code. It works perfectly to display all the records with flower names in place of the flower reference. I’ll see later on, how to modify your code to suit my other query needs.

[JohnSUN]

It is not hard to do in the Query Designer. Just add the table desired amount of times, select the desired relations and change each of them from “inner join” to “left join” (see picture)