How would I construct a query with several OR conditions, where the results are ordered by how many of the conditions match the query?

|
Contents:
will above cmd works?
Include headers when using SELECT INTO OUTFILE?
Is there some way the query in mysql be forward into the file?
and the other way arouind ?
In other words, you need to read file contents and receive it in a quiery result in the client side, right?
You mean query results?
Hello, how would I construct a query with several OR conditions, where the results are ordered by how many of the conditions match the query?
acromegale 2020-09-24 17:38:47
Murali 2020-09-24 17:34:50
Yes my request is same

just use mysqldump

duylk 2020-09-24 17:49:06
I think u can use pt-show-grant to write create user command to text
duylk 2020-09-24 17:49:20
Then copy the command to new mysql server
Murali 2020-09-24 17:50:09
Thank you so much friends
Mr.Robot 2020-09-25 12:55:29
How to add column headers for below query
Mr.Robot 2020-09-25 12:55:38
SELECT quiz_set_chapter_ques.question_id, quiz_set.user_id ,quiz_ans.test_ans_status, quiz_ans.ans_status, quiz_ans.quiz_set_ques_attempt_time ,custom_board.board_name,CASE WHEN rr4.rack_type_id=20 THEN rn4.name WHEN rr5.rack_type_id=20 THEN rn5.name WHEN rr3.rack_type_id=3 THEN rn3.name WHEN rr5.rack_type_id=3 THEN rn5.name WHEN rr3.rack_type_id=20 THEN rn3.name WHEN rr4.rack_type_id=3 THEN rn4.name ELSE rn7.name END AS class_name,

CASE WHEN rr2.rack_type_id=4 THEN rn2.name WHEN rr3.rack_type_id=4 THEN rn3.name WHEN rr4.rack_type_id=4 THEN rn4.name ELSE rn1.name END AS subject_name,

CASE WHEN rr2.rack_type_id=7 THEN rn2.name WHEN rr3.rack_type_id=7 THEN rn3.name WHEN rr4.rack_type_id=7 THEN rn4.name ELSE rn1.name END AS sub_subject_name,

CASE WHEN rr1.rack_type_id=5 THEN rn1.name WHEN rr2.rack_type_id=5 THEN rn2.name ELSE NULL END AS chapter_name,

CASE WHEN rr1.rack_type_id=6 THEN rn1.name ELSE NULL END AS topic_name from quiz_ans

left join quiz_set_chapter_ques on quiz_ans.quiz_set_chapter_ques_id=quiz_set_chapter_ques.quiz_set_chapter_ques_id

left join quiz_set_chapter on quiz_set_chapter_ques.quiz_set_chapter_id=quiz_set_chapter.quiz_set_chapter_id

LEFT JOIN quiz_set On quiz_set_chapter.set_id = quiz_set.set_id

LEFT JOIN custom_board_rack AS cbr ON cbr.rack_id = quiz_set_chapter.board_classs_subject_chapter_id

LEFT JOIN custom_board AS custom_board ON custom_board.`custom_board_id` = cbr.`custom_board_id`

LEFT JOIN resource_rack AS rr1 ON quiz_set_chapter.board_classs_subject_chapter_id =`rr1`.`rack_id`

LEFT JOIN rack_name AS rn1 ON rn1.rack_name_id`=`rr1.rack_name_id

LEFT JOIN resource_rack AS rr2 ON rr2.rack_id`=`rr1.rack_container_id

LEFT JOIN rack_name AS rn2 ON rn2.rack_name_id`=`rr2.rack_name_id

LEFT JOIN resource_rack AS rr3 ON rr3.rack_id`=`rr2.rack_container_id

LEFT JOIN rack_name AS rn3 ON rn3.rack_name_id`=`rr3.rack_name_id

LEFT JOIN resource_rack AS rr4 ON rr4.rack_id`=`rr3.rack_container_id

LEFT JOIN rack_name AS rn4 ON rn4.rack_name_id`=`rr4.rack_name_id

LEFT JOIN resource_rack AS rr5 ON rr5.rack_id`=`rr4.rack_container_id

LEFT JOIN rack_name AS rn5 ON rn5.rack_name_id`=`rr5.rack_name_id

LEFT JOIN resource_rack AS rr6 ON rr6.rack_id`=`rr5.rack_container_id

LEFT JOIN rack_name AS rn6 ON rn6.rack_name_id`=`rr6.rack_name_id

LEFT JOIN resource_rack AS rr7 ON cbr.rack_id`=`rr7.rack_id

LEFT JOIN rack_name AS rn7 ON rn7.rack_name_id`=`rr7.rack_name_id

LEFT JOIN user_other_details uod on uod.user_id=quiz_set.user_id

LEFT JOIN t_user_package tup on quiz_set.user_id=tup.user_id

LEFT JOIN t_package_syllabi tps on tps.id=tup.package_id

LEFT JOIN t_package tp on tp.package_id=tps.package_id

LEFT JOIN t_user_transaction tut on tut.transaction_id=tup.transaction_id

WHERE quiz_set.set_date_added >=’2019-08-01 00:00:00′ and quiz_set.set_date_added <=’2019-08-31 23:59:59′

AND tp.package_category not in(50,52,53)

AND tup.paid_amount>0 and tup.status IN (1,3) and tut.pkg_payment_details = ‘Processed’AND tut.pkg_payment_details != ‘FREE USER’

AND (uod.key_name=’app_name’ and uod.value IN (‘ESSA’,’Learning App’)) AND custom_board.board_name=’CBSE’

AND CASE WHEN (tp.package_category=40 OR tp.package_category=58) THEN tup.parent_user_package_id IS NOT NULL ELSE TRUE END AND CASE WHEN tp.package_category IN (64,65,66,105,106,107) THEN tup.parent_user_package_id IS NULL ELSE TRUE END

Mr.Robot 2020-09-25 12:55:57
Please help me with this
smlkw 2020-09-25 12:56:40
Mr.Robot 2020-09-25 12:55:38
SELECT quiz_set_chapter_ques.question_id, quiz_set.user_id ,quiz_ans.test_ans_status, quiz_ans.ans_status, quiz_ans.quiz_set_ques_attempt_time ,custom_board.board_name,CASE WHEN rr4.rack_type_id=20 THEN rn4.name WHEN rr5.rack_type_id=20 THEN rn5.name WHEN rr3.rack_type_id=3 THEN rn3.name WHEN rr5.rack_type_id=3 THEN rn5.name WHEN rr3.rack_type_id=20 THEN rn3.name WHEN rr4.rack_type_id=3 THEN rn4.name ELSE rn7.name END AS class_name,

CASE WHEN rr2.rack_type_id=4 THEN rn2.name WHEN rr3.rack_type_id=4 THEN rn3.name WHEN rr4.rack_type_id=4 THEN rn4.name ELSE rn1.name END AS subject_name,

CASE WHEN rr2.rack_type_id=7 THEN rn2.name WHEN rr3.rack_type_id=7 THEN rn3.name WHEN rr4.rack_type_id=7 THEN rn4.name ELSE rn1.name END AS sub_subject_name,

CASE WHEN rr1.rack_type_id=5 THEN rn1.name WHEN rr2.rack_type_id=5 THEN rn2.name ELSE NULL END AS chapter_name,

CASE WHEN rr1.rack_type_id=6 THEN rn1.name ELSE NULL END AS topic_name from quiz_ans

left join quiz_set_chapter_ques on quiz_ans.quiz_set_chapter_ques_id=quiz_set_chapter_ques.quiz_set_chapter_ques_id

left join quiz_set_chapter on quiz_set_chapter_ques.quiz_set_chapter_id=quiz_set_chapter.quiz_set_chapter_id

LEFT JOIN quiz_set On quiz_set_chapter.set_id = quiz_set.set_id

LEFT JOIN custom_board_rack AS cbr ON cbr.rack_id = quiz_set_chapter.board_classs_subject_chapter_id

LEFT JOIN custom_board AS custom_board ON custom_board.`custom_board_id` = cbr.`custom_board_id`

LEFT JOIN resource_rack AS rr1 ON quiz_set_chapter.board_classs_subject_chapter_id =`rr1`.`rack_id`

LEFT JOIN rack_name AS rn1 ON rn1.rack_name_id`=`rr1.rack_name_id

LEFT JOIN resource_rack AS rr2 ON rr2.rack_id`=`rr1.rack_container_id

LEFT JOIN rack_name AS rn2 ON rn2.rack_name_id`=`rr2.rack_name_id

LEFT JOIN resource_rack AS rr3 ON rr3.rack_id`=`rr2.rack_container_id

LEFT JOIN rack_name AS rn3 ON rn3.rack_name_id`=`rr3.rack_name_id

LEFT JOIN resource_rack AS rr4 ON rr4.rack_id`=`rr3.rack_container_id

LEFT JOIN rack_name AS rn4 ON rn4.rack_name_id`=`rr4.rack_name_id

LEFT JOIN resource_rack AS rr5 ON rr5.rack_id`=`rr4.rack_container_id

LEFT JOIN rack_name AS rn5 ON rn5.rack_name_id`=`rr5.rack_name_id

LEFT JOIN resource_rack AS rr6 ON rr6.rack_id`=`rr5.rack_container_id

LEFT JOIN rack_name AS rn6 ON rn6.rack_name_id`=`rr6.rack_name_id

LEFT JOIN resource_rack AS rr7 ON cbr.rack_id`=`rr7.rack_id

LEFT JOIN rack_name AS rn7 ON rn7.rack_name_id`=`rr7.rack_name_id

LEFT JOIN user_other_details uod on uod.user_id=quiz_set.user_id

LEFT JOIN t_user_package tup on quiz_set.user_id=tup.user_id

LEFT JOIN t_package_syllabi tps on tps.id=tup.package_id

LEFT JOIN t_package tp on tp.package_id=tps.package_id

LEFT JOIN t_user_transaction tut on tut.transaction_id=tup.transaction_id

WHERE quiz_set.set_date_added >=’2019-08-01 00:00:00′ and quiz_set.set_date_added <=’2019-08-31 23:59:59′

AND tp.package_category not in(50,52,53)

AND tup.paid_amount>0 and tup.status IN (1,3) and tut.pkg_payment_details = ‘Processed’AND tut.pkg_payment_details != ‘FREE USER’

AND (uod.key_name=’app_name’ and uod.value IN (‘ESSA’,’Learning App’)) AND custom_board.board_name=’CBSE’

AND CASE WHEN (tp.package_category=40 OR tp.package_category=58) THEN tup.parent_user_package_id IS NOT NULL ELSE TRUE END AND CASE WHEN tp.package_category IN (64,65,66,105,106,107) THEN tup.parent_user_package_id IS NULL ELSE TRUE END

Please, use pastebin.com for long queries

MasterZiv 2020-09-25 12:57:41
Mr.Robot 2020-09-25 12:55:57
Please help me with this

What do you mean by those “column headers”?

Mr.Robot 2020-09-25 12:58:38
need to export data to csv with column headings
Mr.Robot 2020-09-25 12:58:49
into outfile ‘/var/lib/mysql-files/difficult_aug_2019.csv’ FIELDS TERMINATED BY ‘,’ lines terminated by ‘n’;
Mr.Robot 2020-09-25 12:59:02
will above cmd works?
Mr.Robot 2020-09-25 13:06:39
please help me
acromegale 2020-09-25 13:06:57
read the docs
Master Yoda 2020-09-25 13:07:55
Mr.Robot 2020-09-25 12:58:38
need to export data to csv with column headings

Do it with workbench. Exporting to CSV is easy with that

Mr.Robot 2020-09-25 13:08:22
i use command line there is no workbench
Master Yoda 2020-09-25 13:10:44
Mr.Robot 2020-09-25 13:08:22
i use command line there is no workbench

https://stackoverflow.com/questions/5941809/include-headers-when-using-select-into-outfile

Include headers when using SELECT INTO OUTFILE? – Stack Overflow
Is it possible to include the headers somehow when using the MySQL INTO OUTFILE?
MasterZiv 2020-09-25 13:12:14
Mr.Robot 2020-09-25 13:08:22
i use command line there is no workbench

https://www.mysqltutorial.org/mysql-export-table-to-csv/

MySQL Export Table to CSV – MySQL Tutorial
In this tutorial, you will learn various techniques of how to export MySQL table to CSV file.
Mr.Robot 2020-09-26 16:20:02
Mr.Robot 2020-09-25 12:55:38
SELECT quiz_set_chapter_ques.question_id, quiz_set.user_id ,quiz_ans.test_ans_status, quiz_ans.ans_status, quiz_ans.quiz_set_ques_attempt_time ,custom_board.board_name,CASE WHEN rr4.rack_type_id=20 THEN rn4.name WHEN rr5.rack_type_id=20 THEN rn5.name WHEN rr3.rack_type_id=3 THEN rn3.name WHEN rr5.rack_type_id=3 THEN rn5.name WHEN rr3.rack_type_id=20 THEN rn3.name WHEN rr4.rack_type_id=3 THEN rn4.name ELSE rn7.name END AS class_name,

CASE WHEN rr2.rack_type_id=4 THEN rn2.name WHEN rr3.rack_type_id=4 THEN rn3.name WHEN rr4.rack_type_id=4 THEN rn4.name ELSE rn1.name END AS subject_name,

CASE WHEN rr2.rack_type_id=7 THEN rn2.name WHEN rr3.rack_type_id=7 THEN rn3.name WHEN rr4.rack_type_id=7 THEN rn4.name ELSE rn1.name END AS sub_subject_name,

CASE WHEN rr1.rack_type_id=5 THEN rn1.name WHEN rr2.rack_type_id=5 THEN rn2.name ELSE NULL END AS chapter_name,

CASE WHEN rr1.rack_type_id=6 THEN rn1.name ELSE NULL END AS topic_name from quiz_ans

left join quiz_set_chapter_ques on quiz_ans.quiz_set_chapter_ques_id=quiz_set_chapter_ques.quiz_set_chapter_ques_id

left join quiz_set_chapter on quiz_set_chapter_ques.quiz_set_chapter_id=quiz_set_chapter.quiz_set_chapter_id

LEFT JOIN quiz_set On quiz_set_chapter.set_id = quiz_set.set_id

LEFT JOIN custom_board_rack AS cbr ON cbr.rack_id = quiz_set_chapter.board_classs_subject_chapter_id

LEFT JOIN custom_board AS custom_board ON custom_board.`custom_board_id` = cbr.`custom_board_id`

LEFT JOIN resource_rack AS rr1 ON quiz_set_chapter.board_classs_subject_chapter_id =`rr1`.`rack_id`

LEFT JOIN rack_name AS rn1 ON rn1.rack_name_id`=`rr1.rack_name_id

LEFT JOIN resource_rack AS rr2 ON rr2.rack_id`=`rr1.rack_container_id

LEFT JOIN rack_name AS rn2 ON rn2.rack_name_id`=`rr2.rack_name_id

LEFT JOIN resource_rack AS rr3 ON rr3.rack_id`=`rr2.rack_container_id

LEFT JOIN rack_name AS rn3 ON rn3.rack_name_id`=`rr3.rack_name_id

LEFT JOIN resource_rack AS rr4 ON rr4.rack_id`=`rr3.rack_container_id

LEFT JOIN rack_name AS rn4 ON rn4.rack_name_id`=`rr4.rack_name_id

LEFT JOIN resource_rack AS rr5 ON rr5.rack_id`=`rr4.rack_container_id

LEFT JOIN rack_name AS rn5 ON rn5.rack_name_id`=`rr5.rack_name_id

LEFT JOIN resource_rack AS rr6 ON rr6.rack_id`=`rr5.rack_container_id

LEFT JOIN rack_name AS rn6 ON rn6.rack_name_id`=`rr6.rack_name_id

LEFT JOIN resource_rack AS rr7 ON cbr.rack_id`=`rr7.rack_id

LEFT JOIN rack_name AS rn7 ON rn7.rack_name_id`=`rr7.rack_name_id

LEFT JOIN user_other_details uod on uod.user_id=quiz_set.user_id

LEFT JOIN t_user_package tup on quiz_set.user_id=tup.user_id

LEFT JOIN t_package_syllabi tps on tps.id=tup.package_id

LEFT JOIN t_package tp on tp.package_id=tps.package_id

LEFT JOIN t_user_transaction tut on tut.transaction_id=tup.transaction_id

WHERE quiz_set.set_date_added >=’2019-08-01 00:00:00′ and quiz_set.set_date_added <=’2019-08-31 23:59:59′

AND tp.package_category not in(50,52,53)

AND tup.paid_amount>0 and tup.status IN (1,3) and tut.pkg_payment_details = ‘Processed’AND tut.pkg_payment_details != ‘FREE USER’

AND (uod.key_name=’app_name’ and uod.value IN (‘ESSA’,’Learning App’)) AND custom_board.board_name=’CBSE’

AND CASE WHEN (tp.package_category=40 OR tp.package_category=58) THEN tup.parent_user_package_id IS NOT NULL ELSE TRUE END AND CASE WHEN tp.package_category IN (64,65,66,105,106,107) THEN tup.parent_user_package_id IS NULL ELSE TRUE END

I read that article but I didn’t understand where to add in my script which I mentioned above please modify my script please

saquib_akhtar 2020-09-29 18:34:46
Is there any other tool like mysqlsla
saquib_akhtar 2020-09-29 18:34:53
i need to perform query optimization
saquib_akhtar 2020-09-29 18:35:11
but mysqlsla was deprecated long time back
Master Yoda 2020-09-29 18:37:18
saquib_akhtar 2020-09-29 18:34:53
i need to perform query optimization

You have to use Explain plan to review queries. Index them properly and re write them if necessary

Ankur Tyagi 2020-09-30 23:35:45
Hello
Can someone please share good study reference material for learning MySQL database
Ankur Tyagi 2020-09-30 23:36:14
I plan to learn it from DBA perspective
Vova80 2020-09-30 23:41:44
Documentation
Vova80 2020-09-30 23:42:05
and google
alexsandro_matias 2020-10-01 05:10:19
Is there some way the query in mysql be forward into the file???
alexsandro_matias 2020-10-01 05:12:53
For example
alexsandro_matias 2020-10-01 05:12:54
Load data local infile ‘/home/matias/automatizacao-mysql/tpch-dbgen/data/customer.tbl’ INTO TABLE CUSTOMER FIELDS TERMINATED BY ‘|’;
alexsandro_matias 2020-10-01 05:13:18
A file be loaded into the BD
alexsandro_matias 2020-10-01 05:14:41
and the other way arouind ???
alexsandro_matias 2020-10-01 05:15:55
I should like my select go inside the file on the Linux
MasterZiv 2020-10-01 09:11:35
alexsandro_matias 2020-10-01 05:15:55
I should like my select go inside the file on the Linux

In other words, you need to read file contents and receive it in a quiery result in the client side, right?

Master Yoda 2020-10-01 09:14:40
alexsandro_matias 2020-10-01 05:10:19
Is there some way the query in mysql be forward into the file???

You mean query results?

MasterZiv 2020-10-01 09:18:38
alexsandro_matias 2020-10-01 05:12:54
Load data local infile ‘/home/matias/automatizacao-mysql/tpch-dbgen/data/customer.tbl’ INTO TABLE CUSTOMER FIELDS TERMINATED BY ‘|’;

You need to just load the file into a table inside the DB, and then the data is going to be accessable, if you need to read a changing file, this is not responsibility of DBMS, this is done by other means

alexsandro_matias 2020-10-01 12:06:05
Master Yoda 2020-10-01 09:14:40
You mean query results?

result sets I must put in a file

Akash Singh 2020-10-01 18:55:30
select
#if(cb.barcode=’%BPTRIN%%’,”PENDING”,”DONE”)
i.id,
scl.temp_item_barcode,
mk.code as SKU_Code,
i.item_status,
quality,
carton_barcode,
b.barcode,
scl.created_by,
scl.created_on,
i.last_modified_on
from sticker_correction_line scl
join item i on scl.temp_item_barcode = i.barcode
JOIN myntra_sku.mk_skus mk ON i.sku_id=mk.id
join core_bins b on i.bin_id = b.id
where i.warehouse_id = ‘309’
and scl.temp_item_barcode like (‘%T100%’)
and i.item_status = (‘stored’)
~AND if(cb.barcode=’%BPTRIN%%’,”PENDING”,”DONE”)~
and i.last_modified_on >= ‘2020-10-01 08:00:00’
and i.last_modified_on < ‘2020-10-01 19:00:00’
Akash Singh 2020-10-01 18:55:59
I couldn’t run this query is there any error
sixpeteunder 2020-10-01 19:19:44
Hello, how would I construct a query with several OR conditions, where the results are ordered by how many of the conditions match the query?
sixpeteunder 2020-10-01 19:23:36
E.g if I search for a blue Audi saloon in a cars table(example), all items that are blue, Audi and saloon will be at the top of the list.

Followed by the items that only blue and Audi, or blue and saloon, or Audi and saloon.

Followed by the items that are only either blue, Audi or saloon.

I don’t really have any requirements as to how they are ordered within those groups.

MasterZiv 2020-10-01 19:45:49
Akash Singh 2020-10-01 18:55:30
select
#if(cb.barcode=’%BPTRIN%%’,”PENDING”,”DONE”)
i.id,
scl.temp_item_barcode,
mk.code as SKU_Code,
i.item_status,
quality,
carton_barcode,
b.barcode,
scl.created_by,
scl.created_on,
i.last_modified_on
from sticker_correction_line scl
join item i on scl.temp_item_barcode = i.barcode
JOIN myntra_sku.mk_skus mk ON i.sku_id=mk.id
join core_bins b on i.bin_id = b.id
where i.warehouse_id = ‘309’
and scl.temp_item_barcode like (‘%T100%’)
and i.item_status = (‘stored’)
~AND if(cb.barcode=’%BPTRIN%%’,”PENDING”,”DONE”)~
and i.last_modified_on >= ‘2020-10-01 08:00:00’
and i.last_modified_on < ‘2020-10-01 19:00:00’

select #if … should be select if

MasterZiv 2020-10-01 19:46:29
sixpeteunder 2020-10-01 19:19:44
Hello, how would I construct a query with several OR conditions, where the results are ordered by how many of the conditions match the query?

Hardly you can do this.

Akash Singh 2020-10-01 19:46:34
I used two times
Akash Singh 2020-10-01 19:47:00
If statement I don’t want to run first one
MasterZiv 2020-10-01 19:48:02
sixpeteunder 2020-10-01 19:23:36
E.g if I search for a blue Audi saloon in a cars table(example), all items that are blue, Audi and saloon will be at the top of the list.

Followed by the items that only blue and Audi, or blue and saloon, or Audi and saloon.

Followed by the items that are only either blue, Audi or saloon.

I don’t really have any requirements as to how they are ordered within those groups.

You always can write an explicit order expression and sort according to it, but this is not always doable.

sixpeteunder 2020-10-01 19:48:53
sixpeteunder 2020-10-01 19:23:36
E.g if I search for a blue Audi saloon in a cars table(example), all items that are blue, Audi and saloon will be at the top of the list.

Followed by the items that only blue and Audi, or blue and saloon, or Audi and saloon.

Followed by the items that are only either blue, Audi or saloon.

I don’t really have any requirements as to how they are ordered within those groups.

Just to clarify my example, blue, Audi and saloon are in separate columns.

sixpeteunder 2020-10-01 19:49:47
MasterZiv 2020-10-01 19:48:02
You always can write an explicit order expression and sort according to it, but this is not always doable.

I will look these up.

MasterZiv 2020-10-01 19:50:16
sixpeteunder 2020-10-01 19:48:53
Just to clarify my example, blue, Audi and saloon are in separate columns.

the example is not clear, don’t bother, I understand what you want.

Master Yoda 2020-10-01 20:02:12
alexsandro_matias 2020-10-01 12:06:05
result sets I must put in a file

mysql -u username -p password -e “select * from TBL ;” > /path/to/file . ? Will this do?

|
This website uses cookies to improve user experience. By continuing to use the site, you consent to the use of cookies.