Ask Your Question
0

Two tables DB and difficult Queries [closed]

asked 2017-02-06 16:56:24 +0100

kirsch gravatar image

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 https://ask.libreoffice.org/en/question/72892/grouping-in-base-report-based-on-multiple-fields-with-common-entries/ ?

EXAMPLE DOWNLOAD

http://r.kirsch.free.fr/LibO/FlowerSh...

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-23 20:17:42.791262

1 Answer

Sort by » oldest newest most voted
1

answered 2017-02-06 19:08:47 +0100

JohnSUN gravatar image

updated 2017-02-07 11:12:08 +0100

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"

Some values from single table

edit flag offensive delete link more

Comments

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.

kirsch gravatar imagekirsch ( 2017-02-07 10:56:30 +0100 )edit
1

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)

JohnSUN gravatar imageJohnSUN ( 2017-02-07 11:11:26 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2017-02-06 16:56:24 +0100

Seen: 491 times

Last updated: Feb 07 '17