Get data from 4 different tables

Ultimatum · 01-16-2008, 02:10 AM

Hi, I need a query that checks 4 different tables, to see if there is a match for the username/password filled in the form. 4 tables sounds kinda weird but it's pretty clear. I have 3 different people who can login. Students, companies and admins. Each has there own table with data, but company has 2 tables. 1 for companyname en 1 for departments.

But only the company table en department table are linked together. I have this at the moment but if there is a match from 1 table, I get data from all fields that are mentioned in the select. I hope that you understand, been strugeling with it for some hours now, so hope you can help.

This is the query I have at the moment:

Quote:

SELECT student_id, admin_id
FROM students, admin
WHERE (
(student_id = '8563' AND students.password= MD5('password'))
OR
(admin_id = '8563' AND admin.password= MD5('password'))
)

The output I get:

Quote:

student_id admin_id
8563 25

The combination for student was correct, but because I don't connect the 2 tables I get a result from the other table back aswell. But I don't want that result, only if the combination doesn't match in the table student but in the table admin. So it's impossible to see now in which table the match was.

Hope you still understand, my english sucks a little

Wildhoney · 01-16-2008, 02:20 AM

You could always go for a UNION approach:

sql Code:

SELECT  username, password
FROM    students
WHERE   student_id = '8563'
AND password = MD5('password')
UNION
SELECT  username, password
FROM    admin
WHERE   admin_id = '8563'
AND password = MD5('password')

However, I would be keen to suggest that your table structure is... not wrong, but not well thought out. I think what would have been a better approach would to have put every single individual in one table, and then use bitwise operators to ascertain (to determine) their levels.

Welcome to TalkPHP by the way

I hope you decide to become a regular around here!

Ultimatum · 01-16-2008, 02:27 AM

Hi, thanks for the quick reply. I also looked at UNION, but the problem is, that it takes the fieldnames from the first select, and the fields from the admin table are totally different.

Second, I know that my database design sounds strange, but it's not. I have to do everything in a seperate table cause a company could have multiply departments sign up. So if I put it in 1 table I could end up with 5 times the same data of 1 company. And different companies could have the same departmentnames, so I could have more then once the same departmentname in the table.

And if I make it 1 table, it would have propaly 3 fields, and the 3 field would only be use for companies (department field). So that's not optimalisation.

I optimised my table to the Third normal form, or I did something wrong or it's just impossible for what I want.

Karl · 01-16-2008, 02:45 AM

You could use an AS to rename the columns so that the union treats them as the same column. Such as:

sql Code:

SELECT 
    student_id AS id, 
FROM
    students
WHERE
    student_id = '8563' AND password= MD5('password')

UNION

SELECT 
    admin_id AS id, 
FROM
    admin
WHERE
    admin_id = '8563' AND password= MD5('password')

Ultimatum · 01-16-2008, 02:46 AM

That doesn't work either, I already tried that.
Now I'm getting own the field from the right select back, so we are making progress, but the fieldname from the second select is the same as the alias from the first table in phpmyadmin

CoryMathews · 01-16-2008, 04:46 AM

Is there a way to join these tables, are the linked at all (ie any foreign keys)?

a slightly more difficult work around is to have 4 queries each adding there results to a list or struct, then using that list as if it were the results of one large query. Not the best approach but.. it would work.

Salathe · 01-16-2008, 04:58 AM

You've already said that your tables have been normalised (3NF) but I think your table structure could really do with being improved before moving forward. What do you expect to happen when a student and admin have the same id number (they're in different tables, so it can happen) and the same password (unlikely but again, it can happen). The admin tries to log in but gets logged into the student's account!

I think you'd make things far, far easier for yourself if you broke things down a little more. It would make sense (to me) to have a 'person' or 'user' (or other appropriate name) table which everyone gets put into. Then if the person is a student, a row in the students table can identify them as such or a flag within the person table and the same for any admins.

Ultimatum · 01-16-2008, 12:34 PM

Quote:

Originally Posted by CoryMathews

Is there a way to join these tables, are the linked at all (ie any foreign keys)?

No there isn't.

Quote:

Originally Posted by Salathe

You've already said that your tables have been normalised (3NF) but I think your table structure could really do with being improved before moving forward. What do you expect to happen when a student and admin have the same id number (they're in different tables, so it can happen) and the same password (unlikely but again, it can happen). The admin tries to log in but gets logged into the student's account!

Maybe my example was wrong, but I already covert what you said. Cause students have a so called ovnummer on our school. It's there reversed birthday + a 2 digit number on the end. And they login with that number, a admin logins with the first letter of his/her first name + lastname, and a company logs in with company id. I can upload my excel file, but it's dutch so I don't know if it's has any value then.