LinuxQuestions.org - LISTING entries of mysqldatabase

- Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)

- - LISTING entries of mysqldatabase (https://www.linuxquestions.org/questions/linux-newbie-8/listing-entries-of-mysqldatabase-4175508568/)

LISTING entries of mysqldatabase

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?

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 ...

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.