RWS
RWS

Reputation: 548

Join results from two MySQL tables

I have two MySQL tables from which i can't get joined results.

The first one is just a list of companies and their names:

companies:
____________________________
companyid  |  companyname  |
1               comp1
2               comp2

The second one is the list of user roles:

roles:
_____________________________________________________
roleid  |  uid  | role  |  companyid  |  suspended  |
1           1     dir           1           0
2           1     manag         2           0

I want to get a result that would look like:

_______________________
companyname  |  role  |
comp1           dir
comp2           manag

My query below returns nothing...

SELECT companies.companyid, roles.role FROM companies INNER JOIN roles ON roles.companyid=companies.companyname where (uid = 1 and suspended = 0)

Upvotes: 0

Views: 37

Answers (2)

Janek
Janek

Reputation: 3242

You are joining roles.companyid=companies.companyname which is incorrect since companyname is a string, not an integer.

Try this instead:

SELECT companies.companyid, roles.role FROM companies INNER JOIN roles ON roles.companyid=companies.companyid where (uid = 1 and suspended <> 0);

Upvotes: 1

Suraj
Suraj

Reputation: 3137

Check you joining condition.

Try this,

select a.companyname,b.role from companies a, roles b
    where a.companyid=b.companyid and
    (b.uid = 1 and
    b.suspended <> 0);

Upvotes: 1

Related Questions