Use Conditions In Query [MySQL]
-
Hello there. First let me give the 3 tables' definitions then I ask question. Users UserId UserName Members MemberId MemberName Orders OrderId UserId IsUserMember ItemName In 3rd table, you can see that I am using boolean column which tells whether this Order belongs to guest user or member user. I want to select corresponding data using ONE QUERY. Here is what I have tried so far but it is far from working
SELECT O.*, CASE WHEN O.IsUserMember = 1 THEN U.UserName ELSE M.MemberName END FROM Orders O, CASE WHEN O.IsUserMember = 1 THEN Users U ELSE Members M END WHERE CASE WHEN O.IsUserMember = 1 THEN O.UserId = U.UserId ELSE B.UserId = M.MemberId END;It is giving me this following error:Syntax error near 'CASE WHEN B.IsThisUserMember = 1 THEN Users U ELSE Members M END WHERE CASE WHEN' at line 1NOTE: If I remove first CASE condition, then I getempty set. Thanks for whatever you share. -
Hello there. First let me give the 3 tables' definitions then I ask question. Users UserId UserName Members MemberId MemberName Orders OrderId UserId IsUserMember ItemName In 3rd table, you can see that I am using boolean column which tells whether this Order belongs to guest user or member user. I want to select corresponding data using ONE QUERY. Here is what I have tried so far but it is far from working
SELECT O.*, CASE WHEN O.IsUserMember = 1 THEN U.UserName ELSE M.MemberName END FROM Orders O, CASE WHEN O.IsUserMember = 1 THEN Users U ELSE Members M END WHERE CASE WHEN O.IsUserMember = 1 THEN O.UserId = U.UserId ELSE B.UserId = M.MemberId END;It is giving me this following error:Syntax error near 'CASE WHEN B.IsThisUserMember = 1 THEN Users U ELSE Members M END WHERE CASE WHEN' at line 1NOTE: If I remove first CASE condition, then I getempty set. Thanks for whatever you share. -
Hello there. First let me give the 3 tables' definitions then I ask question. Users UserId UserName Members MemberId MemberName Orders OrderId UserId IsUserMember ItemName In 3rd table, you can see that I am using boolean column which tells whether this Order belongs to guest user or member user. I want to select corresponding data using ONE QUERY. Here is what I have tried so far but it is far from working
SELECT O.*, CASE WHEN O.IsUserMember = 1 THEN U.UserName ELSE M.MemberName END FROM Orders O, CASE WHEN O.IsUserMember = 1 THEN Users U ELSE Members M END WHERE CASE WHEN O.IsUserMember = 1 THEN O.UserId = U.UserId ELSE B.UserId = M.MemberId END;It is giving me this following error:Syntax error near 'CASE WHEN B.IsThisUserMember = 1 THEN Users U ELSE Members M END WHERE CASE WHEN' at line 1NOTE: If I remove first CASE condition, then I getempty set. Thanks for whatever you share.You're looking for a
LEFT JOIN:SELECT
O.*,
CASE
WHEN O.IsUserMember = 1 THEN U.UserName
ELSE M.MemberName
END As Name
FROM
Orders O
LEFT JOIN Users U
ON U.UserId = O.UserId And O.IsUserMember = 0
LEFT JOIN Members M
ON M.MemberId = O.UserId And O.IsUserMember = 1
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Hello there. First let me give the 3 tables' definitions then I ask question. Users UserId UserName Members MemberId MemberName Orders OrderId UserId IsUserMember ItemName In 3rd table, you can see that I am using boolean column which tells whether this Order belongs to guest user or member user. I want to select corresponding data using ONE QUERY. Here is what I have tried so far but it is far from working
SELECT O.*, CASE WHEN O.IsUserMember = 1 THEN U.UserName ELSE M.MemberName END FROM Orders O, CASE WHEN O.IsUserMember = 1 THEN Users U ELSE Members M END WHERE CASE WHEN O.IsUserMember = 1 THEN O.UserId = U.UserId ELSE B.UserId = M.MemberId END;It is giving me this following error:Syntax error near 'CASE WHEN B.IsThisUserMember = 1 THEN Users U ELSE Members M END WHERE CASE WHEN' at line 1NOTE: If I remove first CASE condition, then I getempty set. Thanks for whatever you share.I have got a side-question: shouldn't IsUserMember be a column in the Users table rather than in the Orders one? Is it possible that a specific user is considered as a member for an order, and not a member for another one? (just curious)
Loneliness and cheeseburgers are a dangerous mix.
-
I have got a side-question: shouldn't IsUserMember be a column in the Users table rather than in the Orders one? Is it possible that a specific user is considered as a member for an order, and not a member for another one? (just curious)
Loneliness and cheeseburgers are a dangerous mix.
phil.o wrote:
Is it possible that a specific user is considered as a member for an order, and not a member for another one?
Users represent guest users. Members represent registered users. There are online businesses out there which allow you to shop either as guest user or first register and then continue as member. Having said this, I am not a DB guru. I just started working in it with more serious attitude. Any flaw you see in my design, please let me know.
-
You're looking for a
LEFT JOIN:SELECT
O.*,
CASE
WHEN O.IsUserMember = 1 THEN U.UserName
ELSE M.MemberName
END As Name
FROM
Orders O
LEFT JOIN Users U
ON U.UserId = O.UserId And O.IsUserMember = 0
LEFT JOIN Members M
ON M.MemberId = O.UserId And O.IsUserMember = 1
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
phil.o wrote:
Is it possible that a specific user is considered as a member for an order, and not a member for another one?
Users represent guest users. Members represent registered users. There are online businesses out there which allow you to shop either as guest user or first register and then continue as member. Having said this, I am not a DB guru. I just started working in it with more serious attitude. Any flaw you see in my design, please let me know.
Django_Untaken wrote:
Users represent guest users. Members represent registered users.
But, at the end, they are all users, whether they are registered or not. You do not have any "Members" table. Is it possible that a specific user (so, only one user id), can order as registered user once, and then as unregistered user later (or the contrary)? Again, I do not know the details of your requirements; I may be wrong, as I may miss something important. Kindly :)
Loneliness and cheeseburgers are a dangerous mix.
-
When putting this code section, what language you used in PRE tag? I could not find out how to proper format and paste SQL queries in codeproject. thnx
<pre lang="SQL">
...
</pre>
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
