Problems with join in select statement

[Beauford2016]

I have two tables, an assets and a users table. In both tables there is a UID row. If an asset is assigned to a particular user the UID's in both tables will be the same, if not the UID in the assets table will be 0. So what I am trying to do is select the two tables based on the particular asset and whether or not a user is assigned to it. If there is no user I just display the assets information if there is a user I want to display that as well.

I have tried various different combinations of joins without any luck, below is just one of the failures.

$q = "SELECT * FROM assets t1 join users t2 WHERE t1.hid =".$record." and t1.uid = t2.uid";

hid is the primary key for assets table and is an auto_increment # and I want to see if there are any users associated with this asset based on the uid in both tables. If the uid is 0 in the assets table then no users are associated, If the number is say 2, then it will be associated to the user with that #, and uid is the primary key in the users table.

Hope this makes sense and someone can help me with an answer.

Thanks

[mac_gyver]

27 minutes ago, Beauford2016 said:

I have two tables, an assets and a users table. In both tables there is a UID row. If an asset is assigned to a particular user the UID's in both tables will be the same, if not the UID in the assets table will be 0

you should have three tables. the asset table is where the items are defined, with id, name and any other columns needed to describe each asset. this will assign an asset id. you would then have an asset_user table to store the user(s) who have each asset, with id, asset_id, user_id, and any other columns needed to describe the asset/user data.

you would then use a LEFT JOIN between the asset and the asset_user table, and a LEFT JOIN between that and the users table, to get the assets, plus any optional user information. If there is no user to join with, you will get NULL values for things you select from the asset_user and users tables.

also, please list out the columns you are selecting in your query (this is even more important with multi-table queries) and don't put external, unknown, dynamic data values directly into a sql query statement, use a prepared query.

Edited October 4, 2020 by mac_gyver

[Barand]

I take it that the user->asset relationship is 1 to many, but an asset can only be assigned to one user? Hence two tables.

You need somethng like this

$q = "SELECT t1.hid
           , t1.description
           , t2.name
      FROM assets t1
           LEFT JOIN
           users t2 ON t1.uid = t2.uid
      WHERE t1.hid = ?         
     ";

(I'll move this to the MySQL forum)

[Beauford2016]

Thanks Barands, that works perfectly. I knew I was in the right area but just couldn't get it exactly. Thanks also mac_guyver for your response.