Hot answers tagged

3

About 4.5 years ago, I wrote a rather aggressive stored procedure to traverse data stored in a hierarchy and bring back all descendants within that table : Find highest level of a hierarchical field: with vs without CTEs I took the code from the GetFamilyTree function and wrote it as a procedure for you STORED PROCEDURE DELIMITER $$ DROP PROCEDURE IF ...


2

Simple answer: If you want both date columns to be between the input dates: SELECT category FROM tablename WHERE 'start_date' BETWEEN start_date AND end_date AND 'end_date' BETWEEN start_date AND end_date ; Or if you want any of the date columns to be between the input dates: SELECT category FROM tablename WHERE 'start_date' BETWEEN start_date AND ...


2

You can't directly without drawing in all the tables. You would need to first pick out the table name needed then use ad-hoc SQL or call a specific procedure per table to get the data out. Drawing in all the tables as follows would work: SELECT ac.carID, ac.type, ac.commonAttribute1, ac.commonAttribute2 , ct.specialAttributeA , ...


2

This might run faster due to "lazy evaluation". Note that you want to fetch some large columns, yet thousands of rows need to be looked at before deciding which 10 are desired. Instead of gathering all the columns needed, let's get just the PRIMARY KEYs, then reach back into posts only 10 times to get the bulky columns. Note that bulky columns are stored ...


2

With your datasets, MySQL has to obtain those 450,000 records from posts (in 1000 little chunks from each matching source_id), sort it, and then return the top 10. It is a costly exercise. You could resort to using a stored procedure, and accumulate results going back in time, say daily or weekly, looping until obtaining at least 10 records, and then ...


2

There are a couple of options here. One is to repeat the given structure for each column: select if(EmpNum is null,"",ID) as s_id, if(EmpNum is null,"",Name) as s_name, .. etc from users where isActive=1; This will return a row for every row in the table, showing empty strings for employees and values for students. If the objective is to return ...


1

I figured it out, had to group by trucn(invdate,'mm') and count the separate columns SELECT TO_CHAR(TRUNC(inv.invdate,'MM'),'MON-RR') "Month", COUNT(DISTINCT(inv.order_no)) "Orders", COUNT(inv.order_no) "Order Lines", COUNT(DISTINCT(inv.invoice_no)) "Invoices", COUNT(inv.invoice_no) "Invoice Lines", ...


1

In such case you don't have to use MERGE. Assuming data1 column can be used to check whether the record exists, the following should do the job : INSERT INTO A (data1, data2, dataX) SELECT B.data1, B.data2, B.data3 FROM B b WHERE NOT EXISTS ( SELECT NULL FROM A a WHERE a.data1=b.data1 );


1

Table1: A (DATA1, DATA2, DATA3) Table2: B (DATA1, DATA2, DATA3) Assumption: First fieldName of both table is primary key MERGE INTO A TA USING (SELECT * FROM B) TB ON (TA.DATA1 = TB.DATA1) WHEN NOT MATCHED THEN INSERT (TA.DATA1, TA.DATA2, TA.DATA3) VALUES (TB.DATA1, TB.DATA2, TB.DATA3);


1

Instead of 1424, perhaps you should use t4.t2id. Instead of 1046, if the parent exists in table4 or any of the others, use that link instead of the constant. You could also use a single IN, and a UNION of both queries. Finally, if you use a filter condition in WHERE on a LEFT JOINed table, that defeats the purpose of a LEFT JOIN and might result in bugs. ...



Only top voted, non community-wiki answers of a minimum length are eligible