Cross apply

Hello, this is the first time I post topics. I find it very useful though.

I wrote because I am encountering difficulties in using the feature "cross apply". I've been working with 9.3.0.4984 HeidiSQL version. As executing the code it returns the error since the current system version doesn't recognize the feature "cross apply", I was wondering which the corresponding code for that specific command was.

With "Cross apply", widely employed in sql codes and broadly discussed in sql online tutorial videos, I'd like to join one table (one specicific column) with once function or stored procedure previously created and correctly stored.

Thank you for helping me out.

You should post some query to start with, otherwise noone is probably able to help you.

Thank you for the quick answer, unfortunately I think I don't know how to post a query, otherwise I'd have certaintly done that. From the forum page I can create only "New topics".

Thank you again.

Oh, my misunderstanding. I thought you meant to post/issue on the website a request of discussion. Anyway... here I provide a simple example to clarify what I mean:

I would like to create a stored procedure, something like that:

CREATE PROCEDURE p_employee 
(
IN custID INT
)

SELECT si.invoiceID, si.customerID,
        sd.ProductID, sd.Quantity,
        cp.ProductName,cp.RetailPrice

FROM SalesInvoice as si
JOIN SalesInvoiceDetail AS sd ON si.InvoiceID=sd.InvoiceID
JOIN CurrentProducts AS cp ON cp.ProductsID=sp.ProductsID

WHERE si.CustomerID=custID

if I wanted to run the procedure I'd have to type as follows:

CALL p_employee(x)

where "x" would be the input parameter custID,corresponding to the ID of a specific customer for whom I want to know the output informations.

But what if I wanted to execute the stored procedure for 10000 customers simultaneously? Literature includes one function named "cross apply" through which I could pass as input parameter ("x") one entire column, extracted from another table.

SELECT *
FROM customer AS cu CROSS APPLY p_employee(cu.CustomerID)
ORDER BY cu.CustomerID
WHERE cu.Income >= (select median(Income) from cu)

The issue is all about the "CROSS APPLY" function which in 9.3.0.4984 HeidiSQL version is not included, as well as the "median" function or a "quantile" functions to compute continuous or descrete distribution quantiles. In fact, if I type those functions in my sql sheet they won't be recognized as functions and the code will return errors accordingly.

Any suggestion on how to execute the functions reported likewise?

Thank you in advance

Ok, you need to differentiate HeidiSQL (the client) from MySQL (the server). If you get errors on a certain query, then this is normally due to the server returning that error, not the client.

However, I don't know nothing about that CROSS APPLY, but I just found a thread on stackoverflow saying that MySQL does not support that directly.

Thank you for the answer. Apparently as you said the function is not supported.

Given the above reported query, any suggestion on how to work out the task of running the procedure for more than one custID at once?

Again, how can I implement the median and quantile functions?

Thank you again