home hardware prices news articles forums photos user reviews
Go Back   Tech Support Forums - TechIMO.com > PC Hardware and Tech > Webmastering and Programming
Ask a Tech Support Question (free)!

Database: Comparing values in two different tables

Reply
Get bargains at  »  Dealighted.com
 
Thread Tools Search this Thread
Currently Active Users: 1666
Discussions: 200,924, Posts: 2,379,111, Members: 246,290
Old August 1st, 2009, 08:05 PM   Digg it!   #1 (permalink)
Member
 
Join Date: Mar 2005
Posts: 87
Question
Database: Comparing values in two different tables

Hello all,

I hope someone can answer this question!

I am trying to compare two columns from a table with one column from another table. I need to have the rows from the first table that aren't contained in the second table.

The "problem" is that I have around 800K records on each table.

Has anyone tried to do this in a home computer (2 Gb RAM 1.79 GHz Centrino Duo)?

If you have, how long did it take?

If you haven't, what's your guess? How long will it take?

The DBMS is MySQL running on Windows.

Thank you all!!!
Crisstina is offline   Reply With Quote
Old August 1st, 2009, 08:43 PM     #2 (permalink)
Not Really a Member
 
Join Date: Oct 2001
Posts: 25,384
how long it will take is heavily dependant on if the columns are indexed

I think mysql now supports 'join' syntax?

you could do something as simple as
select *
from t1
left outer join t2
on t1.column1 = t2.column1

obviously changing the table/column names

this is written for t-sql so some minor syntax changes maybe necessary for mysql

what you're asking for is a pretty common outer join
it maybe a right outer join, so if left doesn't give you the correct results try a right outer join
__________________
Helicopters don't fly; they vibrate so much and make so much noise that the earth rejects them.
vass0922 is online now   Reply With Quote
Old August 9th, 2009, 10:58 AM     #3 (permalink)
Caveat Emptor
 
Rootstonian's Avatar
 
Join Date: Mar 2005
Location: Out of my mind
Posts: 3,241
Send a message via AIM to Rootstonian
Code:
 I've always done:
select
t1.column1,
t1.column2
from table t1
where not exists (select 'x'
                              from table t2
                              where t1.column1 = t2.column
                             and      t1.column2  = t2.column)
Rootstonian is offline   Reply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need values TheBigCarp321 Processors, Memory, and Overclocking 5 May 2nd, 2006 11:13 PM
CHS values on my HD are incorrect. hav0c Storage Related 1 January 5th, 2006 04:41 AM
Family values Theophylact IMO Community 7 March 9th, 2004 02:15 AM
Conversion Values washe Graphic Design and Digital Photography 3 August 12th, 2002 08:17 PM


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Most Active Discussions
Is It Just Me? (2858)
Obama the Muslim (14)
California Passes Anti-Flat-HDTV Le.. (39)
Is the PSU I received dead? (10)
windows vista security holes (9)
Foreign voltage (10)
HIS HD5770 graphic card question (15)
Print spooler problem (13)
Install XP pro and a Vista laptop ?.. (9)
Dept. of HS: NSA 'Helped' Develop V.. (15)
A good PSU? (10)
New Computer wont recognize XP disc (7)
Ideal cheap graph card for PC-Gamin.. (15)
EVGA 9800 gtx help with finding a g.. (8)
Recent Discussions
Optical Audio A-B Switch (0)
windows vista security holes (9)
Fire in DVD (0)
radeon x850xt platinum & shader 3 (2)
The NTDVM CPU has encountered an ille.. (24)
[F@H SPAM 11/16/09] ! 1/2 months to r.. (34)
Wireless speakers for PC? (11)
Print spooler problem (13)
Help getting around port 80 for camer.. (2)
Display shows 3x5 inch in middle of s.. (3)
monitor will not turn on at all, (1)
World's largest Monopoly Game using G.. (331)
Foreign voltage (10)
FiOS modem/router interfering with ne.. (7)
Browsers wont load websites (2)
Virus Doctor Popup? (1)
Dept. of HS: NSA 'Helped' Develop Vis.. (15)
Install XP pro and a Vista laptop ?? (9)
EVGA 9800 gtx help with finding a goo.. (8)
Modern Warfare For the PC (32)
Problem with speed step/turbo boost? (1)
Modern Warfare 2: Who Bought It? (61)
SIS 740 and Widescreen (8)
Baffling Problem with my CPU/MoBo's. .. (0)
HIS HD5770 graphic card question (15)


All times are GMT -4. The time now is 11:01 PM.
TechIMO Copyright 2009 All Enthusiast, Inc.



1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28