Click here to Skip to main content
15,868,141 members
Home / Discussions / Database
   

Database

 
GeneralRe: I need this windows odbc driver Pin
Member 1458760611-Sep-19 8:16
Member 1458760611-Sep-19 8:16 
AnswerRe: I need this windows odbc driver Pin
Maciej Los11-Sep-19 8:47
mveMaciej Los11-Sep-19 8:47 
QuestionLooking for some assistance with a query Pin
FrankLepkowski10-Sep-19 9:50
FrankLepkowski10-Sep-19 9:50 
AnswerRe: Looking for some assistance with a query Pin
MadMyche10-Sep-19 11:12
professionalMadMyche10-Sep-19 11:12 
GeneralRe: Looking for some assistance with a query Pin
MadMyche11-Sep-19 1:56
professionalMadMyche11-Sep-19 1:56 
GeneralRe: Looking for some assistance with a query Pin
FrankLepkowski11-Sep-19 4:15
FrankLepkowski11-Sep-19 4:15 
GeneralRe: Looking for some assistance with a query Pin
MadMyche11-Sep-19 6:28
professionalMadMyche11-Sep-19 6:28 
QuestionNeed help mixing two queries in one... Pin
Joan M1-Sep-19 0:40
professionalJoan M1-Sep-19 0:40 
Hello,

First of all sorry for putting like a code dump here... I'll try to explain it as clearly as possible, but given my inexperience with SQL (years and years without touching it) I think it is better if I post the two queries completely rather than asking a generic question.
I have two SQL queries that work well.
I need to mix them and I don't know how to do it.
The first one returns me all the details of the Invoice and the customer name, and the second one gives me the invoice cost after making the right selections and calculations (more on this after).

tInvoices fields are: id, invoiceNumber, invoiceSeries, date, customerId, notSent

tCustomers fields are: id, name, ...

And the first query is simple:
SQL
SELECT tInvoices.*, tCustomers.name FROM tInvoices INNER JOIN tCustomers ON CustomerId = tCustomers.id;
That way I get all the invoice details and the customer name in each row to show it in an HTML table.

But I also want to put the invoice price in that table and this is much more complicated:
I have a structure in which there is a reference price per customer, project and user... this means that depending on what's agreed with each customer for each project, each user will have a cost...
If I go to a customer company to program something I could get X€ and other guy from the company could get Y€. And those proces could be V€ and W€ for another project for the same customer...
Appart of that I've set in each task a special cost field. If that cost is filled (not NULL) then I'll use that cost as an exception (again something pacted with the customer).
All this said... this happens with the tasks (work done in a project by a human being) and with the expenses (hotel, gasoline, meals... tickets in general).

These are the tables involved:

tTasks fields are: id, projectId, userId, taskTypeId, taskUseId, invoiceId, date, startTime, endTime, price, notes

tReferencePricesForTasks fields are: projectId, userId, taskTypeId, price

tTasksTypes fields are: id, name

tExpenses fields are: id, projectId, userId, expenseTypeId, invoiceId, date, quantity, price, notes

tReferencePricesForExpenses fields are: projectId, userId, espenseTypeId, price

tExpensesTypes fields are: id, name, allowEditingAmounts

And the second query is not that simple, but seems to work well:
SQL
SELECT SUM(subTotal) AS total FROM
    (
      SELECT
          (
            CASE WHEN tTasks.taskUseId = 1 THEN
            (
              CASE WHEN price IS NULL THEN
              (
                SELECT
                  tReferencePricesForTasks.price
    
                FROM tReferencePricesForTasks
                WHERE tReferencePricesForTasks.taskTypeId = tTasks.taskTypeId AND
                      tReferencePricesForTasks.projectId = tTasks.projectId AND
                      tReferencePricesForTasks.userId = tTasks.userId
              ) 
              ELSE 
                price
              END
            )
            ELSE
              0
            END
          ) 
          *
          (
          SELECT
            ROUND(TIME_TO_SEC(TIMEDIFF(endTime, startTime)) / 3600,2)
          ) 
          AS subTotal 
    
      FROM
        tTasks
        
      LEFT JOIN tTasksTypes ON tTasks.taskTypeId = tTasksTypes.id
    
      WHERE tTasks.taskUseId <> 3 AND
            tTasks.invoiceId = '.$PHP_Invoice_ID_VALUE.'
    
    UNION ALL 
    
      SELECT
          (
            CASE WHEN price IS NULL THEN
            (
              SELECT
                ROUND(tReferencePricesForExpenses.preu,2)
    
              FROM tReferencePricesForExpenses
              WHERE tReferencePricesForExpenses.expenseTypeId = tExpenses.expenseTypeId AND
                    tReferencePricesForExpenses.projectId = tExpenses.projectId AND
                    tReferencePricesForExpenses.userId = tExpenses.userId
            ) 
            ELSE 
              ROUND(cost, 2)
            END
          ) 
          *
          round(tExpenses.quantity,2)
          AS subTotal
        
    
      FROM
        tExpenses
        
      LEFT JOIN tExpensesTypes ON tExpenses.expenseTypeId = tExpensesTypes.id
    
      WHERE tExpenses.invoiceId = '.$PHP_Invoice_ID_VALUE.'
    )
    AS VIEW1
As you can see in my second query, I'm using $PHP_Invoice_ID_VALUE which is a PHP variable that I update in each row of the first query to get the Invoice ID.

I'd like to mix both queries, but I don't know how to do it.

The idea is not needing to call the second query in each row... that way everything should be faster and I would be able to apply filters and orders given the price too...
I would like to substitute the PHP variables for each invoice listed in the first query.

Using Mysql and MariaDB.

Sorry for the code dump, but I truly don't know how to ask it generically without missing any detail.

Thank you very much for your help.
AnswerRe: Need help mixing two queries in one... Pin
Richard Deeming2-Sep-19 1:02
mveRichard Deeming2-Sep-19 1:02 
GeneralRe: Need help mixing two queries in one... Pin
Jörgen Andersson2-Sep-19 1:39
professionalJörgen Andersson2-Sep-19 1:39 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 10:24
professionalJoan M2-Sep-19 10:24 
GeneralRe: Need help mixing two queries in one... Pin
Jörgen Andersson2-Sep-19 19:27
professionalJörgen Andersson2-Sep-19 19:27 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 19:29
professionalJoan M2-Sep-19 19:29 
GeneralRe: Need help mixing two queries in one... Pin
Jörgen Andersson2-Sep-19 19:35
professionalJörgen Andersson2-Sep-19 19:35 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 10:21
professionalJoan M2-Sep-19 10:21 
GeneralRe: Need help mixing two queries in one... Pin
phil.o2-Sep-19 10:43
professionalphil.o2-Sep-19 10:43 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 11:24
professionalJoan M2-Sep-19 11:24 
GeneralRe: Need help mixing two queries in one... Pin
phil.o2-Sep-19 12:25
professionalphil.o2-Sep-19 12:25 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 18:52
professionalJoan M2-Sep-19 18:52 
GeneralRe: Need help mixing two queries in one... Pin
Mycroft Holmes3-Sep-19 12:40
professionalMycroft Holmes3-Sep-19 12:40 
GeneralRe: Need help mixing two queries in one... Pin
Joan M4-Sep-19 9:24
professionalJoan M4-Sep-19 9:24 
GeneralRe: Need help mixing two queries in one... Pin
Joan M4-Sep-19 9:25
professionalJoan M4-Sep-19 9:25 
GeneralRe: Need help mixing two queries in one... Pin
Mycroft Holmes4-Sep-19 12:37
professionalMycroft Holmes4-Sep-19 12:37 
GeneralRe: Need help mixing two queries in one... Pin
Mycroft Holmes2-Sep-19 12:31
professionalMycroft Holmes2-Sep-19 12:31 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 19:27
professionalJoan M2-Sep-19 19:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.