1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2017
    Rep Power

    Pulling From Multiple Tables

    I have a query for an inventory system which looks for all assets which belong to a user (in this example, jdoe) and then pulls the matching information from the remaining tables. The problem is my query pulls much more data than it should and I believe it is because it is not matching everything up to the user. Below is my query. I would appreciate any input on what I am doing wrong.

    SELECT categories.category_name, assets.asset_tag, manufacturers.manufacturer_name, models.model_name, models.model_number, assets.serial FROM assets, models, manufacturers, categories WHERE assets.samAccountName = 'jdoe' AND assets.model_id = models.id AND manufacturers.id = models.manufacturer_id AND models.category_id = categories.id
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2017
    Rep Power
    You need to learn how to us JOIN.
  4. #3
  5. No Profile Picture
    Super Moderator
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jun 2009
    Hartford, WI
    Rep Power
    Edit: benanamen beat my post... NJ...

    Just as a reference: https://dev.mysql.com/doc/refman/5.7/en/join.html

    With what you have provided, you pretty much have 4 totally separate queries running. If you have heard of JOINing tables in order to make a SELECT similar to this, that is not what you want. When you list all tables within your FROM as so, each request is being placed on the entire content of each table.

    Via a few JOINs, what you'd be doing is putting these tables together as a new table, built in a manner that would suit your request at its best. Most databases hold a primary ID in one manner or another. I'm assuming/hoping all the items on your list here having something in common, whether it be directly or not. As far as your JOIN goes, this would be defined within your JOIN's ON definition.

    'SELECT t2.`name`, t1.`comment`, t1.`value`, t1.`limit`, t1.`date` FROM `balance` AS t1 LEFT JOIN `account` AS t2 ON t1.`acc` = t2.`id` WHERE t1.`type` = 1;
    This only links 2 tables, and the definitions is what makes a bit more of a secure "hard" link. The balance table has its own set of IDs. As well as most any other table of mine. The account table has unique IDs. A column within the balance holds an ID via `acc` in relation to a matching ID from the account table. This way, every invoice from that table simply holds the ID from the account table it is in relation to.
    LEFT JOIN `account` ON balance.`acc` = account.`id`
    Last edited by Triple_Nothing; December 19th, 2017 at 08:38 PM.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.

IMN logo majestic logo threadwatch logo seochat tools logo