Databases
StackOverflow2013
Postsdbo
POMySQL multiple query from multiple databases
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL multiple query from multiple databases
    primarykey
    data
    text
    <p>How can I merge these two queries into 1? Both are using 2 different databases but are all on same server. Would I use sub-selects or union here?</p> <p>I basically would want something like to display the following:</p> <pre><code>User name | Total A | Total B jon 5 3 tim 4 2 sarah 3 1 </code></pre> <p>Here are the queries:</p> <p>First query:</p> <pre><code>SELECT U.USR_USERNAME AS `User Name`, COUNT(rp_workflow.PIG.APP_UID) AS 'Total A' FROM rp_workflow.PMT_INSP_GRID PIG JOIN rp_workflow.PMT_INSP_NORMAL PIN ON PIG.APP_UID = PIN.APP_UID JOIN wf_workflow.USERS U ON PIN.USR_UID_ORDER = U.USR_UID GROUP BY U.USR_USERNAME </code></pre> <p>Second query:</p> <pre><code>SELECT U.USR_USERNAME AS `User Name`, COUNT(rp_workflow.PIQG.APP_UID) AS 'Total B' FROM rp_workflow.PMT_INSP_QC_GRID PIQG JOIN rp_workflow.PMT_INSP_NORMAL PIN ON PIQG.APP_UID = PIN.APP_UID JOIN wf_workflow.USERS U ON PIN.USR_UID_QC = U.USR_UID WHERE PIN.APP_STATUS = 'Completed' GROUP BY U.USR_USERNAME </code></pre> <p><strong>Edit:</strong> tried a subselect but it ended up only displaying the sum of all values for 2nd query. Feel like i'm close but also getting more confused.</p> <pre><code>SELECT U.USR_USERNAME AS `User Name`, COUNT(rp_workflow.PIG.APP_UID) AS 'Total A', (SELECT COUNT(rp_workflow.PIQG.APP_UID) FROM rp_workflow.PMT_INSP_QC_GRID PIQG JOIN rp_workflow.PMT_INSP_NORMAL PIN ON PIQG.APP_UID = PIN.APP_UID JOIN wf_workflow.USERS U ON PIN.USR_UID_QC = U.USR_UID WHERE PIN.APP_STATUS = 'Completed') AS 'Total B' FROM rp_workflow.PMT_INSP_GRID PIG JOIN rp_workflow.PMT_INSP_NORMAL PIN ON PIG.APP_UID = PIN.APP_UID JOIN wf_workflow.USERS U ON PIN.USR_UID_ORDER = U.USR_UID GROUP BY U.USR_USERNAME </code></pre> <p>What it displayed:</p> <pre><code>User name | Total A | Total B jon 5 6 </code></pre> <p><strong>Edit 2: *the USR_UID are not present in tables PIG or PIQG so they must be joined to tables PIN. From there, the username can be retrieved</strong></p> <p>tried this query but ended up getting an unknown column error:</p> <pre><code> SELECT wf_workflow.U.USR_USERNAME AS `User Name` , (SELECT count(PIG.APP_UID) FROM rp_workflow.PMT_INSP_GRID PIG JOIN rp_workflow.PMT_INSP_NORMAL PIN ON PIG.APP_UID = PIN.APP_UID AND PIN.USR_UID_ORDER = U.USR_UID) AS 'Total A', (SELECT count(PIQG.APP_UID) FROM rp_workflow.PMT_INSP_QC_GRID PIQG JOIN rp_workflow.PMT_INSP_NORMAL PIN2 ON PIQG.APP_UID = PIN2.APP_UID AND PIN2.USR_UID_ORDER = wf_workflow.U.USR_UID WHERE PIN2.APP_STATUS = 'Completed') AS 'Total B' FROM wf_workflow.USERS U </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjustWired
    1. USjustWired
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL multiple query from multiple databases
      1. This table or related slice is empty.
      1. VTDownMod
    1. COTry putting database name just before table name like this. - `[Db Name].[Table Name]`
      singulars
      1. POMySQL multiple query from multiple databases
      1. USRavindra Gullapalli
    2. CO@MikeBrant I tried a subselect but it only displayed the sum of all values for the 2nd query. see first post
      singulars
      1. POMySQL multiple query from multiple databases
      1. USjustWired
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload