Bad practice? Use inner join id for future joined tables

Question

I have 2 or more tables that will frequently be used for JOINs in database VIEWs but they don't have any field in common so I'm considering adding the same generated id key to be inserted into a "join_id" field of 3 different tables to be joined later. I searched the internet regarding this type of topic but don't see much discussion so I'm cautious if it's good practice or not. Is it safe to proceed to do this kind of database operation?

---

EDIT1

Here's more details on what I want to accomplish

SCENARIO

DEPT-A has their own table They will create a new row/record with flds1, flds2, flds3, innerjoin_id

DEPT-B has their own table with flds4, flds5, flds6, innerjoin_id

When DEPT-A creates a new row, a rule will be triggered to create a new row with same innerjoin_id in DEPT-B table also.

DEPT-B needs to see DEPT-A flds1, flds2, flds3 and be able to edit their respective flds4, flds5, flds6.

DEPT-A web input form will see flds1, flds2, flds3 (editable)

DEPT-B web input form will see flds1-readonly, flds2-readonly, flds3-readonly, flds4-editable, flds5-editable, flds6-editable

EXAMPLE:

DEPT-A enters "apple", "banana", "cherry"

DEPT-B needs to see apple, banana, cherry and then in fld4-6 enter "applebanana", "bananacherry", "cherryapple"

DEPT-B is dependent on DEPT-A flds1-3

If DEPT-B cannot see "apple","banana","cherry" then they don't know what to input for flds4, flds5, flds6

Answer 1

Specifically for MySQL...

When you JOIN two tables, you specify how the tables are related.

• (The common case.) ... FROM cities JOIN countries ON countries.id = cities.country_id -- This a many:1 relationship (many cities in one country).
• (Rare.) ... FROM a CROSS INNER JOIN b (without anything relating the two tables) -- This gives you all combinations of the rows of a and of b. In MySQL, the keyword CROSS is optional.
• (Sometimes) ... FROM a LEFT JOIN b ON ... WHERE b.id IS NULL -- This finds the rows of a for which the corresponding row of b is missing.

ON and WHERE have the same semantics for INNER JOIN and CROSS JOIN. But it helps the reader to use ON to show the relationship (whether or not there is a FOREIGN KEY) and to use WHERE for 'filtering'.

Any number of tables can be JOINed together, in SELECT, in a VIEW, in a UPDATE, or in a DELETE.

A VIEW is syntactic sugar for a SELECT. Essentially all rules that apply to one apply to the other.

To one of your key questions: There is no requirement that a column be in 'common' to JOIN on them. Can you guess what results from this strange JOIN:

SELECT us_states.state_name 
    FROM us_states
    JOIN countries ON us_states.state_name =
                      countries.country_name;

Hint: The one answer begins with 'G'.

Answer 2

Selective permissions cannot be done with JOIN.

Use VIEWs to allow certain users (based on login to MySQL) to write vs read-only vs not-even-see columns of table(s).

Use GRANTs to the users on specific columns of specific tables.

You can probably handle readonly with Grants. But to allow a user to see only certain columns may require separate Views.