Joining two SQL Tables with no relationship?

问题:

I have 2 tables in a MySQL database. One is a legacy table (landlords) which has a field ('landlord_id').

The other is a Joomla! 1.7 table called jos_users which is needed to ensure all users are valid Joomla! users and can login, etc. It has a field called 'id'.

As an example I have an existing record in landlords.landlord_id = 2 (John Smith), however there is no corresponding jos_user.id record because the landlords table pre existed before the jos_users.

Is there a way to establish / create a relationship between the two using SQL?

Or

Do I need to create jos_user records for the landlords (and if so how?)?

Any suggestions or examples are greatly appreciated.

回答1:

You will have to port across the data from the old system to the new system. I assume that you're using Joomla's user login system right? In which case the data will have to be in the format and place joomla is looking for it in, if you understand.

You can't really create a "link" without replicating data.

What you CAN do, which I suggest, is to add a field onto the landlord's table which is joomla_user_id. This way you can associate a joomla user with a landlord record from your old system. That way you don't have to manually port anything across, and you can build the app to get the landlord data based on their joomla user id.

回答2:

You can actually join them

SELECT
  lls.landlord_id,
  jus.id
FROM landlords lls
LEFT JOIN jos_users jus ON lls.landlord_id=jus.id

that will return you ALL Landlords and joins the matching Joomla Users. If there is no Joomla User for a given Landlord, the jus.id will be NULL. The non-matching entries are NULL and do not get removed from the result set because of the LEFT JOIN. Simply spoken a LEFT JOIN keeps all rows from the left table and inserts NULLs if the match fails.

Sample:

landlord_id  |  id
          1  |    1
          2  |    2
          3  | NULL