If statement inside query based on other table's data

[jimmyoneshot]

My site has 2 types of users - business users and regular users each of these has the same basic data i.e. id, username and password however the rest of the data is completely unique to that type of user therefore I use a users table for the regular login and userid data and 2 tables for the rest of the data like so:-

 

PROFILES

 

id  |  username  |  password  |  usertype

 

REGULAR_USER_DETAILS

 

name  |  surname  |  address  |  age  etc

 

BUSINESS_USER_DETAILS

 

company  |  contact  |  fax  etc

 

 

 

In the users table a usertype of 1 represents a regular user and 2 represents a business user.

 

When a user logs in I want to get all their details from the users table and then all the details from the appropriate details table based on the kind of user that is logging in ALL IN 1 QUERY so my query would do something like this:-

 

SELECT * FROM users

 

IF (users.usertype == 1){

    SELECT * FROM regular_user_details

}ELSE IF(users.usertype == 2){

    SELECT * FROM business_user_details

}

 

Is this possible?

[AyKay47]

you will want to use a case statement

 

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

[kickstart]

Hi

 

Possible but messy as you will have different numbers of columns returned depending on the type of user.

 

You could do this:-

 

SELECT *
FROM users
LEFT OUTER JOIN regular_user_details ON users.id = regular_user_details.userid
LEFT OUTER JOIN business_user_details ON users.id = business_user_details.userid

 

Obviously this will return columns from both tables, with null values in the columns that do not match that rows user type.

 

If you do this make sure you specify the columns rather than just use SELECT *

 

All the best

 

Keith

[jimmyoneshot]

Aha good point. Thanks for the link.

 

What I'm wondering though is how can I use the usertype value retrieved earlier in this same query as the case value?

 

i.e.

 

SELECT * FROM users

 

CASE  ???

 

WHEN ??? == 1 THEN 

    SELECT * FROM regular_user_details

[WHEN ??? == 2 THEN

    SELECT * FROM business_user_details

]

 

If I have that syntax right that is?

[jimmyoneshot]

Cheers Keith. That makes perfect sense and was the way I was originally going to go but was just wondering if the if statement/case statement way was possible as it seems best for my system because eventually I am going to have even more user types each with their own detail tables too meaning the result using multiple joins connecting to all detail tables may eventually get bigger and bigger and bigger

[AyKay47]

CASE  usertype
WHEN 1 THEN SELECT * FROM regular_user_details
WHEN 2 THEN SELECT * FROM business_user_details
END CASE;

[kickstart]

Hi

 

Don't think it is possible.

 

Probably be easier to just dynamically build separate queries for each one as each is going to need to have separate processing to cope with the different numbers of columns.

 

All the best

 

Keith

[jimmyoneshot]

I tried the suggested code:-

 

SELECT * FROM users WHERE id = 1

CASE users.usertype
WHEN 1 THEN SELECT * FROM regular_user_details
WHEN 2 THEN SELECT * FROM business_user_details
END CASE;

 

which  resulted in an error:-

 

"check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE users.usertype WHEN 1 THEN SELECT * FROM regular_user_details END CASE LIM "

 

Should this code work?

[kickstart]

Hi

 

Can't see how that would work.

 

If this was possible (and fairly certain it isn't) then the syntax would be something like

 

SELECT * 
FROM users 
INNER JOIN 
CASE users.usertype
WHEN 1 THEN SELECT * FROM regular_user_details
WHEN 2 THEN SELECT * FROM business_user_details
END CASE
WHERE users. id = 1

 

But even if it did work it would probably result in fairly hideous php to go with it.

 

I would either do the JOIN, or just set up different selects

 

If if the columns you require from the other tables are consistent

 

SELECT users1.blah, details.someotherblah 
FROM users INNER JOIN regular_user_details details
UNION
SELECT users1.blah, details.someotherblah 
FROM users INNER JOIN business_user_details details
UNION
SELECT users1.blah, details.someotherblah 
FROM users INNER JOIN escort_user_details details

 

All the best

 

Keith

[jimmyoneshot]

Hi Keith. Thanks for the help. I made some new tables for testing since my last reply hence the "z_" but I ended up with this:-

 

SELECT z_users.account_type,

CASE WHEN z_users.account_type = 1 THEN

z_user_details.name

ELSE

z_business_details.company

END

FROM z_users

LEFT JOIN z_user_details ON z_user_details.id1 = z_users.id

WHERE z_users.id = 1

 

This seems to work however I want to set the column names inside those case statements AS something like this:-

 

SELECT z_users.account_type,

CASE WHEN z_users.account_type = 1 THEN

z_user_details.name AS l_name

ELSE

z_business_details.company AS l_company

END

FROM z_users

LEFT JOIN z_user_details ON z_user_details.id1 = z_users.id

WHERE z_users.id = 1

 

but the parts with the "AS" result with a "...the right syntax to use near 'AS l_name..."

 

Can you see anything wrong with that last statement?

[kickstart]

Hi

 

Try putting the AS after the END, using a common name for both.

 

You will also need both tables

 

SELECT z_users.account_type,
CASE
WHEN z_users.account_type = 1 THEN z_user_details.name
ELSE z_business_details.company
END AS someName
FROM z_users
LEFT JOIN z_user_details ON z_user_details.id1 = z_users.id
LEFT JOIN z_business_details ON z_business_details.id1 = z_users.id
WHERE z_users.id = 1

 

All the best

 

Keith

[jimmyoneshot]

GAHD! More problems. In that last query I only selected one column per case statement as a simplified example but I actually want to select several like this:-

 

SELECT z_users.account_type,

CASE WHEN z_users.account_type = 1 THEN

z_user_details.name, z_user_details.surname

ELSE

z_business_details.company, z_business_details.contact

END

FROM z_users

LEFT JOIN z_user_details ON z_user_details.id1 = z_users.id

WHERE z_users.id = 1

 

but this produces an error so I'm not sure if our As staement will work now  :'(

[kickstart]

Hi

 

Really think that doing this is just making code that will be a nightmare to read for no real purpose.

 

Think you would need a separate case statement for each column.

 

All the best

 

Keith

[jimmyoneshot]

It certainly seems that way unless there is a way to implement brackets or something. Difficult stuff for sure. Cheers for the help anyway. Will post back if I figure it out

[AyKay47]

use a join in this case..

[jimmyoneshot]

Hi AyKay47. How would I do that with the above code? Or do you mean I should just use a join overall i.e. :-

 

SELECT *

FROM users

LEFT OUTER JOIN user_details ON users.id = user_details.id1

LEFT OUTER JOIN business_details ON users.id = business_details.id1

[AyKay47]

yeah just a join overall, i myself have not used the mysql case statement so I do not the ins and outs of the syntax, however for your case you would be better off using a join

[fenway]

If you know that only one of the two columns will be not-NULL, you can use COALESCE().

[jimmyoneshot]

Hi fenway. How exactly would I do that in my code though? I'm not so familiar with the sytax and how to apply it.

 

@AyKay47 Yep I may have to go with a join for the time being as all else seems to be failing.

[AyKay47]

to be honest im not familiar with that sql function either...fenway is the mysql man, however here is the documentation on correct syntax etc...

 

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

[kickstart]

Hi

 

Something like this

 

SELECT users1.blah, COALESCE(a.somecol,b.anothercol,c.fredscol) AS FirstCol , COALESCE(a.joescol,b.lolcol,c.billscol) AS SecondCol 
FROM users 
LEFT OUTER JOIN regular_user_details details a
LEFT OUTER JOIN business_user_details details b
LEFT OUTER JOIN escort_user_details details c

 

However seems a lot of effort to avoid returning a load of null rows.

 

All the best

 

Keith

[fenway]

Oh, it's far from preferable -- it's just cleaner than CASE when NULL is known.

[jimmyoneshot]

Some interesting solutions but I can't seem to figure out how to get them working. I think the least painful way is to simply include all the possible user fields inside the users table so the users table would become:-

 

id  |  username  |  password  |  usertype  |  name  |  surname  |  address  |  age  |  company  |  contact  |  fax

 

And if for example a user was a regular user then in that row company contact and fax would be blank.

 

Or if a user was a business user then in that row name, surname and age would be blank.

 

Is this the best way of doing this if I need to get everything in 1 query? I think it's a strong case for breaking db rules for improving front end performance and limiting server calls.

 

How would you guys do it if you were me?

[fenway]

Keeping them in a single table prevents you from having multiple values for each "primary record" -- e.g. address, company, contact, etc.

[kickstart]

Hi

 

Not sure that solves anything.

 

In addition to Fenways point, if you did this not only are you bringing back null fields (exactly the same at using a LEFT OUTER JOIN on each of the possible related tables), but you are also storing these null fields on the database.

 

All the best

 

Keith