title: query with join across multiple databases-syntax error
body: <p>I have 2 databases namely db1,db2. I need a query that fetch the data from these dbs(db1,db2) which have inturn 2 tables(concessions,invoicing) each.</p>
  
  <p>In db1.concessions => concession is primary key.
     db1.invoicing   => [Concession Number] is primary key</p>
  
  <p>similarly in db2.concessions => concession is primary key.
               db2.invoicing   => [Concession Number] is primary key</p>
  
  <p><strong>In database1</strong></p>
  
  <p>db1.tbl1 =>  Concessions table has data</p>
  
  <pre><code>    concession
      TH-123
      TH-456
      FP-789
      NZ-609
  </code></pre>
  
  <p>db1.tbl2 =>  invoicing table has data</p>
  
  <pre><code>    [Concession Number]          invoiced_on
      TH-322                        10.09.10
      TH-900                        23.10.10
      FP-675                        04.05.09
      NZ-111                        19.11.08
  </code></pre>
  
  <p>luckily, in a database the value of concession in unique. i.e concessions.[concession] = invoicing.[concession Number] yields no data..</p>
  
  <p><strong>In database2:</strong></p>
  
  <p>db1.tbl1 =>  Concessions table has data</p>
  
  <pre><code>    concession
      TH-123
      FP-789
      NZ-999
      TH-900
  </code></pre>
  
  <p>db1.tbl2 =>  invoicing table has data</p>
  
  <pre><code>    [Concession Number]          invoiced_on(dd.mm.yy)
      TH-456                        18.01.06
      TH-777                        23.10.04
      FP-675                        03.05.09
      NZ-149                        26.11.08
  </code></pre>
  
  <p>HEre in db2 concession is unique, concessions.[concession] = invoicing.[concession Number] yields no data..</p>
  
  <p>Now the query should fetch the records that have common 
  db1.(concessions.concession OR invoicing.concession number) = db2(concessions.concession OR invoicing.concession number)</p>
  
  <p>In the sample data it should return, TH-123,FP-789,NZ-999, FP-675.</p>
  
  <p><strong>My 2nd</strong> question is there is possibility of extending this query to multiple database. I can't change the count of databases to 1 as they are already <strong>fixed</strong>. Please let me know the best procedure for the same.</p>
  
  <p>I tried something like this, there are syntax errors,</p>
  
  <pre><code>SELECT a.concession as db1_CON_NUMBER FROM db1.dbo.concessions as a  UNION 
  SELECT b.[Concession Number] as db1_CON_NUMBER  FROM db1.dbo.invoicing as b 
  INNER JOIN 
  SELECT c.concession as db2_CON_NUMBER FROM db2.dbo.concessions as c  UNION 
  SELECT d.[Concession Number] as db2_CON_NUMBER  FROM db2.dbo.invoicing as d 
  ON db1_CON_NUMBER = db2_CON_NUMBER 
  </code></pre>
  
  <p>Hope you will answer both the questions.
  Thanks for your patience in reading such a long mail!</p>