CB LIST can i use the sql union all command here?
- eddyd
- OFFLINE
-
New Member
- Posts: 15
- Thanks: 2
- Karma: 0
4 years 3 weeks ago #317505
by eddyd
CB LIST can i use the sql union all command here? was created by eddyd
Hi there,
i'm new with CB and have bought some addons as query, condition, auto actions.
Now i'm trying to use the union command in my list as you can see because in my sql it gives me the perfect result when i do this :
SELECT `firstname`,`lastname` FROM `tcf2019_comprofilerbackup` WHERE `cb_zomerreeks` = "dubbel" UNION ALL SELECT `firstname`,`lastname` FROM `tcf2019_comprofilerbackup` WHERE `cb_zomerreeks2` = "dubbel" UNION ALL SELECT `firstname`,`lastname` FROM `tcf2019_comprofilerbackup` WHERE `cb_zomerreeks3` = "dubbel"
This works fine (what i also need)
(cb_zomerreeks="dubbel" OR cb_zomerreeks2="dubbel" OR cb_zomerreeks3="dubbel") AND (cb_betaald="Ja") AND (cb_zomerladder="Ja")
What i try is this and the system gives me an error by tellin me that the union command is not aloud :
(cb_zomerreeks="dubbel" ) AND (cb_betaald="Ja") AND (cb_zomerladder="Ja") UNION ( cb_zomerreeks2="dubbel" ) AND (cb_betaald="Ja") AND (cb_zomerladder="Ja") UNION ( cb_zomerreeks3="dubbel") AND (cb_betaald="Ja") AND (cb_zomerladder="Ja")
I hope that i can use the union command and that i made a syntax error.
Can you help me please?
Best regards,
Eddy
i'm new with CB and have bought some addons as query, condition, auto actions.
Now i'm trying to use the union command in my list as you can see because in my sql it gives me the perfect result when i do this :
SELECT `firstname`,`lastname` FROM `tcf2019_comprofilerbackup` WHERE `cb_zomerreeks` = "dubbel" UNION ALL SELECT `firstname`,`lastname` FROM `tcf2019_comprofilerbackup` WHERE `cb_zomerreeks2` = "dubbel" UNION ALL SELECT `firstname`,`lastname` FROM `tcf2019_comprofilerbackup` WHERE `cb_zomerreeks3` = "dubbel"
This works fine (what i also need)
(cb_zomerreeks="dubbel" OR cb_zomerreeks2="dubbel" OR cb_zomerreeks3="dubbel") AND (cb_betaald="Ja") AND (cb_zomerladder="Ja")
What i try is this and the system gives me an error by tellin me that the union command is not aloud :
(cb_zomerreeks="dubbel" ) AND (cb_betaald="Ja") AND (cb_zomerladder="Ja") UNION ( cb_zomerreeks2="dubbel" ) AND (cb_betaald="Ja") AND (cb_zomerladder="Ja") UNION ( cb_zomerreeks3="dubbel") AND (cb_betaald="Ja") AND (cb_zomerladder="Ja")
I hope that i can use the union command and that i made a syntax error.
Can you help me please?
Best regards,
Eddy
Please Log in to join the conversation.
krileon
Team Member- OFFLINE
- Posts: 68515
- Thanks: 9087
- Karma: 1434
4 years 3 weeks ago #317533
by krileon
Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
Replied by krileon on topic CB LIST can i use the sql union all command here?
We can not provide custom coding assistance. Your issue isn't due to the plugin or anything of the sort. You need to provide valid SQL syntax. See the below documentation regarding unions.
dev.mysql.com/doc/refman/5.7/en/union.html
In short each union select query should be surrounded by parentheses.
dev.mysql.com/doc/refman/5.7/en/union.html
In short each union select query should be surrounded by parentheses.
Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
Please Log in to join the conversation.
- eddyd
- OFFLINE
-
New Member
- Posts: 15
- Thanks: 2
- Karma: 0
4 years 3 weeks ago #317546
by eddyd
Replied by eddyd on topic CB LIST can i use the sql union all command here?
Hi there,
thx for your answer.
I understand that you can not provide custom coding assistance but when i'm executing this code , following the link that you gave me, it works perfect in the sql database :
SELECT `firstname`,`lastname` FROM `tcf2019_comprofilerbackup` WHERE `cb_zomerreeks` = "dubbel" UNION ALL SELECT `firstname`,`lastname` FROM `tcf2019_comprofilerbackup` WHERE `cb_zomerreeks2` = "dubbel" UNION ALL SELECT `firstname`,`lastname` FROM `tcf2019_comprofilerbackup` WHERE `cb_zomerreeks3` = "dubbel"
When i'm trying to do this in CB for creating a list, it is not possible with the next code in the filters/advanced tab:
(`cb_zomerreeks` = "dubbel") UNION ALL ( `cb_zomerreeks2` = "dubbel")
When i'm changing , in the above line, UNION by OR it works but the the list in cb doesn't output the same name multiple times.
But when you say it is possible, can you provide me a manual instead of the link you gave me so i can look it up.
I have been trying now for 2 hours to get the list in cb that i want but for me it is not possible. I used parentheses everywhere but no result.
Really hope you can help me.
Best regards,
Eddy
thx for your answer.
I understand that you can not provide custom coding assistance but when i'm executing this code , following the link that you gave me, it works perfect in the sql database :
SELECT `firstname`,`lastname` FROM `tcf2019_comprofilerbackup` WHERE `cb_zomerreeks` = "dubbel" UNION ALL SELECT `firstname`,`lastname` FROM `tcf2019_comprofilerbackup` WHERE `cb_zomerreeks2` = "dubbel" UNION ALL SELECT `firstname`,`lastname` FROM `tcf2019_comprofilerbackup` WHERE `cb_zomerreeks3` = "dubbel"
When i'm trying to do this in CB for creating a list, it is not possible with the next code in the filters/advanced tab:
(`cb_zomerreeks` = "dubbel") UNION ALL ( `cb_zomerreeks2` = "dubbel")
When i'm changing , in the above line, UNION by OR it works but the the list in cb doesn't output the same name multiple times.
But when you say it is possible, can you provide me a manual instead of the link you gave me so i can look it up.
I have been trying now for 2 hours to get the list in cb that i want but for me it is not possible. I used parentheses everywhere but no result.
Really hope you can help me.
Best regards,
Eddy
Please Log in to join the conversation.
- krileon
- Team Member
- OFFLINE
- Posts: 68515
- Thanks: 9087
- Karma: 1434
4 years 2 weeks ago #317572
by krileon
Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
Replied by krileon on topic CB LIST can i use the sql union all command here?
You can not do that in userlists Advanced Sorting or Filtering. Those parameters directly tell you what they extend. Sorting adds to the ORDER BY statement of the query. Filtering adds to the WHERE statement of the query. You can not replace the entire userlist query with your own. If you just want to filter off cb_zomerreeks, cb_zomerreeks2, and cb_zomerreeks3 then the below should work fine in Advanced Filtering.
( `cb_zomerreeks` = "dubbel" OR `cb_zomerreeks2` = "dubbel" OR `cb_zomerreeks3` = "dubbel" )Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
Please Log in to join the conversation.
- eddyd
- OFFLINE
-
New Member
- Posts: 15
- Thanks: 2
- Karma: 0
4 years 2 weeks ago #317579
by eddyd
Replied by eddyd on topic CB LIST can i use the sql union all command here?
Thx for the explanation. Now i know it's not possible what i was trying and i understand why.
Best regards,
Eddy
Best regards,
Eddy
The following user(s) said Thank You: krileon
Please Log in to join the conversation.
Moderators: beat, nant, krileon
Time to create page: 0.216 seconds
-
You are here:
- Home
- Forums
- Support and Presales
- Single Add-on Support
- CB LIST can i use the sql union all command here?