dependent join on first table field

[tonyh79]

Hello all,
I hope someone can help me with this one.

I have a user system that handles different user types. My main table is users_login which has their log in info plus the user type, and then I have users_typeA, users_typeB, users_typeC... to hold the info particular to that user type.

I need to write a query on the users_login table and join the corresponding user type table. Is this possible to accomplish in the same query?

Thank you in advance!.

 

[PHV]

Something like this ?
SELECT U.*, A.*, B.*, C.*
FROM users_login U
LEFT JOIN users_typeA A ON U.userPK = A.userFK AND U.type = 'A'
LEFT JOIN users_typeB B ON U.userPK = B.userFK AND U.type = 'B'
LEFT JOIN users_typeC C ON U.userPK = C.userFK AND U.type = 'C'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tonyh79]

Well, I wish I didn't have to join all tables. I have four different user types and each has close to 20 fields. Joining all the tables would probably be slower than just the one needed.

 

[AlexCuse]

maybe it would be faster to build a derived table by putting your user type tables into a union ALL query, and then join to that?

I don't think that the 3 left joins would be slow at all though. What is your DBMS?

Ignorance of certain subjects is a great part of wisdom

 

[tonyh79]

I'm running mysql 4. I guess I could do that. But since a user can only be of one type, it's a waste to have to join all three types.

Anyway, what you guys suggest works and I'll stick to that for now.

Thanx a lot for your comments.

 

[r937]

But since a user can only be of one type, it's a waste to have to join all three types.

no, not really


it wastes nothing because only one of the joins will actually return anything

letting the database test each table (which will have an index on the type) is going to be a lot faster than you writing conditional application code to test the type and then join to the appropriate table -- now, that would be a real waste ;-)


r937.com | rudy.ca