SQL syntax

[bennystromberg]

I need help with a SQL-syntax.
I´m coding a web-application in PHP and I´m using a MySQL database. My web-application is going to show information about people working at different companies. Each company have their own table in the database (for example Microsoft, IBM, HP) and then I have one table called 'users'. In the companies tables there are two columns: 'firstname' and 'surname'. In the 'users' table there are also two columns: 'username' and 'company'. When a persons logs in to the web-application the code checks which username logged in and I would like to use this code: SELECT firstname, surname FROM (SELECT company FROM users WHERE username='the one who is logged in');
Unfortunately MySQL can´t handle this kind of syntax. Do you have any ideas how I could get around this problem?

Thanks a lot!
Benny
Finland

[raf]

welcome here.


Unfortunately MySQL can´t handle this kind of syntax
From mySQL version 4.1 on, mySQL suppors subquerys (or subselects)
But the emample you give, well, i haven't tried that one yet (not in any db-format, so i don't even know if it would be valid.)
I doubt that the 1 table per company is the best designoption, but what do i know since i don't know the content.

But you can just split it up and first run the select on the userstable -->just select the company when you proces the login, so you don't need an extra query, ans store it in a sessionvariable.