Run mysql query from two tables

Posted on 2011-10-14
Last Modified: 2012-05-12
I am trying to create a report but having some issues, I need to pull a from a department column in one table and then take those results and use the login column from that to search in the second table. Can anyone help me with this? Thanks
Question by:maximus81
    LVL 14

    Expert Comment

    by:Giovanni Heward
    You'll need to use a JOIN statement... example:

    select * from `db`.`login_table` l
    join `db`.`department_table` d on d.`id`=l.`dept_id`;

    Open in new window


    Author Comment

    So what would that look like with this information.

    Database ATS

    Table - atsusers - Colunms - department, login
    Table - atsassets - Colunms - asset, asignedto

    I want to display the asset name from atsassets and would like to search using the department, this would give me all the logins from atsusers table, next I want to search the atsassets asignedto colunm using the logins from atsusers. This is so confusing.
    LVL 59

    Accepted Solution

    As stated above, it sounds like you want a JOIN.

    SELECT a.asset, a.asignedto
    FROM atsassets a
    JOIN atsusers u ON u.login = a.asignedto
    WHERE u.department = 'departement you want to search'


    Author Closing Comment

    Thank you so much, you have saved me hours of trying to figure this out.

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now