Using INNER JOIN With Four or More Tables

Wappler General Need Help
1

What’s the best way to avoid problems when one of the four JOINed tables is empty. The page renders fine until I add the fourth table. The only reason I can think of is due to that table being empty.

What I’m trying to do is this:

Each form, once filled out, will be stored in the database. I want a status button that will change from a TODO state to a COMPLETED state.

I was thinking that I could use the ID in each table row of that new record to trigger the HIDE / SHOW for that particular button.

The initial table (starts the process), once it is filled out, that records ID is passed on to the next table as a FK. I am trying to use the initial table’s ID as a JOIN for all other attached tables.

Everything works fine until I JOIN a table that has no data. At which time the complete page renders as a blank page.

12%20AM
Screen Shot 2018-08-31 at 9.01.12 AM.png1326×199 25.3 KB

There may be another way to do this, but I have ran into a dead-end street.

Any resolutions or comments would be very appreciated. Thanks!

2

INNER JOIN show result only of value are present in all table.

I did something similar using 4 different queries and showing/hiding buttons using condition if query has item or not

2 Likes
3

Depending on your query you probably need a LEFT (OUTER) JOIN or RIGHT (OUTER) JOIN. Like @updates already is saying, the INNER JOIN only returns results when it matches all tables. See the different types of joins at https://www.w3schools.com/sql/sql_join.asp.

2 Likes
4

Thanks! I knew the addition of the empty table was causing the error. I just could NOT recall the reason why.

I will give an Outer Join a go…

Thanks a million!

5

If I change code below from “inner” to “outer” it should work… you think?

        "wheres": [],
        "orders": [],
        "joins": [
          {
            "type": "inner",
            "table": "frm_inbound_career_signatures",
            "clauses": [
              {
                "table": "frm_inbound_career_signatures",
                "column": "career_outbound_id",
                "bool": "and",
                "operator": "=",
                "value": {
                  "table": "frm_inbound_career_initiate",
                  "column": "career_inbound_id"
                }
              }
            ]
          }
        ]
6

Please use the UI in order to change the joins and do not alter the code by yourself.

7

Obviously, I see now that I overlooked the opportunity to change the JOIN type when the Database Table Joins popup was shown.

8

you can try with views in MySQL to make easier query later…

9

Great idea… although I have created views in the same database, I never thought of using them for that!

1 Like
10

You can change your join type anytime you like by just opening the query clicking the table and clicking the pencil icon

1 Like
11

Done… Thanks for your assistance!

Staff's%20Status%20Page
Staff's Status Page.png1367×1035 151 KB

3 Likes
12

Looks good JR! Nice clean design as well!

13

Thank you very much!

14

Looks really nice! Congrats

15

Hi Guys

although its a old post but my question is similar. Can any one advise how can i use FULL JOIN in Wappler UI. i can see only three options there.

image

16

Hi Yannick,

How about using the custom query action where you can type in your own sql?

—Ken

17

Hi Ken yes i did write the query but query became too long and further below feature does not work properly. record dont filter with each letter typed. records only shows up when when make full selection example. i need to type “AR1” in text filed and usually records filter down with each letter types “A”, “AR” then “AR1” but when i used custom query and build query. the dynamic function only works until i enter full “AR1” nothing happens letter by letter

18

Wappler query designer only supports joins which are supported on both MS SQL server and MySQL server.

As MySQL does NOT support full joins it is not supported by the query designer.

As an alternative use a custom query

19

Thanks Brian. What i did alternatively, i used views in MySql, created full union in view and used view and data source in Wappler. this turned easy for my case

1 Like
20

Great idea!