LISTING entries of mysqldatabase

wackysiya · 06-19-2014, 10:49 PM

Hi

I have 2 databases which has same tablesnames and I need to list entries of a particular table in database1 not existing in the same tablename of database2.

eg : I have db1 which has table name orders

first_id | code | start | end | amountscore | id | amount_class |
+--------+--------+----------+----------+----------+--------------+--------------+
| 1 | ww | 935002 | 935100 | 245 | Ath | boeing |
| 1 | e | 4406486 | 4406628 | 575 | Lth | boeing |
| 1 | r | 30313484 | 30313585 | 348 | SSU | boeing |
| 18 | f | 44787276 | 44787468 | 1338 | SSU | boeing |
| 1 | g | 44787462 | 44787856 | 2775 | Ath | target |
| 1 | m | 44787855 | 44787944 | 747 | Ath | boeing |
| 1 | n | 53636773 | 53636856 | 549 | SSU | target |
| 1 | m | 53888147 | 53888282 | 695 | SSU | genev |
| 1 | q | 53888312 | 53888394 | 399 | Ath | boeing |
| 1 | c | 53888396 | 53888428 | 251 | Ath | geneva |
| 1 | f | 53888461 | 53888589 | 583 | gth | geneva

I only want to list those entries from table name orders in database 1 not present in same table name orders in database 2 .The only condition is I only want to list those entries whose amount_class ='boeing'

Therefore I am interested in those entries having amount_class = "boeing" in table (amount_class) of DB1 not present in same table name in DB2

I tried

Code:

select * 
from (
      select *
      from DB1.orders
      except
      select *
      from DB2.orders
     ) as amountclass
;

Am I missing something?

chrism01 · 06-22-2014, 03:43 AM

See the query example here http://www.toadworld.com/platforms/m...derations.aspx.
Your issue is you haven't defined what you mean by 'not present' ie you have to choose 1 or more cols to say 'and t1.x != t2.x' and so forth ...

wackysiya · 06-22-2014, 04:09 PM

To make it clear

my table in database1 looks like

Quote:

first_id | code | start | end | amountscore | id | amount_class |
+--------+--------+----------+----------+----------+--------------+--------------+
| 1 | ww | 935002 | 935100 | 245 | Ath | boeing |
| 1 | e | 4406486 | 4406628 | 575 | Lth | boeing |
| 1 | r | 30313484 | 30313585 | 348 | SSU | boeing |
| 18 | f | 44787276 | 44787468 | 1338 | SSU | boeing |
| 1 | g | 44787462 | 44787856 | 2775 | Ath | target |
| 1 | m | 44787855 | 44787944 | 747 | Ath | boeing |
| 1 | n | 53636773 | 53636856 | 549 | SSU | target |
| 1 | m | 53888147 | 53888282 | 695 | SSU | genev |
| 12 | q | 53888312 | 53888394 | 399 | Ath | boeing
| 12 | q | 53888312 | 3894 | 9 | Ath | boeing
| 14 | q | 588312 | 53888394 | 3 | Ath | boeing

2nd database has entries

Code:

first_id | code | start | end | amountscore | id | amount_class |
+--------+--------+----------+----------+----------+--------------+--------------+
| 18 | f | 44787276 | 44787468 | 1338 | SSU | boeing |
| 1 | g | 44787462 | 44787856 | 2775 | Ath | target |
| 1 | m | 44787855 | 44787944 | 747 | Ath | boeing |
| 1 | n | 53636773 | 53636856 | 549 | SSU | target |
| 1 | m | 53888147 | 53888282 | 695 | SSU | genev |
| 1 | q | 53888312 | 53888394 | 399 | Ath | boeing |
| 1 | c | 53888396 | 53888428 | 251 | Ath | geneva |
| 1 | f | 53888461 | 53888589 | 583 | gth | geneva 
| 12 | q | 53888312 | 53888394 | 399 | Ath | boeing

So now if you compare both tables in two databases , the 2nd database doesnt have the following entries.

Quote:

| 1 | ww | 935002 | 935100 | 245 | Ath | boeing |
| 1 | e | 4406486 | 4406628 | 575 | Lth | boeing |
| 1 | r | 30313484 | 30313585 | 348 | SSU | boeing |
| 12 | q | 53888312 | 3894 | 9 | Ath | boeing
| 14 | q | 588312 | 53888394 | 3 | Ath | boeing

So basically i need to compare two tables having the amount_class equal to "boeing" and list those entries bbased on first_id.

eg: For first_id belongigng to "1", we would enlist only those entries whose amount_class ='boeing' and which are not present in database 1
by checking the start and end values.

sorry for not being clear earlier.