Jump to content

Problems with join in select statement


Beauford2016

Recommended Posts

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

Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

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)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.