LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 06-19-2014, 10:49 PM   #1
wackysiya
LQ Newbie
 
Registered: Mar 2013
Posts: 12

Rep: Reputation: Disabled
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?
 
Old 06-22-2014, 03:43 AM   #2
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.9, Centos 7.3
Posts: 17,356

Rep: Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367
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 ...
 
Old 06-22-2014, 04:09 PM   #3
wackysiya
LQ Newbie
 
Registered: Mar 2013
Posts: 12

Original Poster
Rep: Reputation: Disabled
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.

Last edited by wackysiya; 06-22-2014 at 09:53 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
sftp ls command, short listing vs long listing slufoot80 Linux - General 3 02-05-2013 10:40 AM
listing of files on CD zdwc01 Linux - Newbie 1 11-06-2011 03:19 PM
rsync mysqldatabase "Permission denied" duckdream Linux - Newbie 1 06-17-2009 02:46 AM
how to sort output at latest entries without disturbing the previous entries record nabmufti Programming 4 02-11-2008 11:36 PM
i need a php pdf tutorial and also MySQLdatabase pdf tutorial dlux45 Linux - Newbie 1 12-27-2007 10:32 AM


All times are GMT -5. The time now is 12:53 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration