Click here to Skip to main content
14,303,639 members
   

Database

 
PinnedHOW TO ANSWER A QUESTION PinPopular
Chris Maunder16-Jul-09 3:10
adminChris Maunder16-Jul-09 3:10 
PinnedHow to get an answer to your question PinPopular
Chris Maunder10-Nov-05 16:30
adminChris Maunder10-Nov-05 16:30 
QuestionMoving from access DB to Oracle to calculate average upon request Pin
Member 1447460719-Sep-19 11:12
memberMember 1447460719-Sep-19 11:12 
AnswerRe: Moving from access DB to Oracle to calculate average upon request Pin
Gerry Schmitz19-Sep-19 12:26
mveGerry Schmitz19-Sep-19 12:26 
QuestionMySQL database Pin
Alboyz17-Sep-19 16:46
memberAlboyz17-Sep-19 16:46 
AnswerRe: MySQL database Pin
#realJSOP18-Sep-19 1:08
mve#realJSOP18-Sep-19 1:08 
GeneralRe: MySQL database Pin
Alboyz18-Sep-19 16:58
memberAlboyz18-Sep-19 16:58 
AnswerRe: MySQL database Pin
Mycroft Holmes18-Sep-19 12:44
memberMycroft Holmes18-Sep-19 12:44 
GeneralRe: MySQL database Pin
Alboyz18-Sep-19 16:58
memberAlboyz18-Sep-19 16:58 
QuestionDatabase suddenly slow Pin
Super Lloyd17-Sep-19 15:39
memberSuper Lloyd17-Sep-19 15:39 
AnswerRe: Database suddenly slow Pin
CHill6018-Sep-19 0:05
protectorCHill6018-Sep-19 0:05 
GeneralRe: Database suddenly slow Pin
Super Lloyd18-Sep-19 20:47
memberSuper Lloyd18-Sep-19 20:47 
AnswerRe: Database suddenly slow Pin
Richard Deeming18-Sep-19 1:01
mveRichard Deeming18-Sep-19 1:01 
GeneralRe: Database suddenly slow Pin
Super Lloyd18-Sep-19 20:47
memberSuper Lloyd18-Sep-19 20:47 
GeneralRe: Database suddenly slow Pin
Mycroft Holmes19-Sep-19 13:14
memberMycroft Holmes19-Sep-19 13:14 
QuestionI need this windows odbc driver Pin
Member 1458760611-Sep-19 8:00
memberMember 1458760611-Sep-19 8:00 
AnswerRe: I need this windows odbc driver Pin
OriginalGriff11-Sep-19 8:01
protectorOriginalGriff11-Sep-19 8:01 
GeneralRe: I need this windows odbc driver Pin
Member 1458760611-Sep-19 8:16
memberMember 1458760611-Sep-19 8:16 
AnswerRe: I need this windows odbc driver Pin
Maciej Los11-Sep-19 8:47
protectorMaciej Los11-Sep-19 8:47 
QuestionLooking for some assistance with a query Pin
FrankLepkowski10-Sep-19 9:50
memberFrankLepkowski10-Sep-19 9:50 
AnswerRe: Looking for some assistance with a query Pin
MadMyche10-Sep-19 11:12
mveMadMyche10-Sep-19 11:12 
GeneralRe: Looking for some assistance with a query Pin
MadMyche11-Sep-19 1:56
mveMadMyche11-Sep-19 1:56 
GeneralRe: Looking for some assistance with a query Pin
FrankLepkowski11-Sep-19 4:15
memberFrankLepkowski11-Sep-19 4:15 
GeneralRe: Looking for some assistance with a query Pin
MadMyche11-Sep-19 6:28
mveMadMyche11-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:
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:
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.

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.