0

I have very limited knowledge of Mysql and queries as my db was set up for me and I being left to run the site. I have need of only one query and have spent hours trying to adapt a statement that would perform as needed. Hope someone is willing to do this. My db admin is now only my host.

Mysql version 5.0 has two tables that I work with.

Table A (very large) has the following columns:

SpeciesID - Normal - Latin - Category - Code - View - CAAB - ITIS

Table B (small table) has the following columns:

Normal - Latin - Category - Code - View - CAAB - ITIS

I want to find the matches from table A and table B and create a table C containing those matches with Latin and SpeciesID only.

I know I need a join in the statement and maybe a match but have not been able to work them out at all.

Thank you for any help with this.

user3499879
  • 1
  • Which columns are you matching between the two tables? Which columns should be put in Table C? – Barmar Apr 04 '14 at 22:44
  • Welcome to SO. Please give the code you had tried, desired output example with initial data example and more specified error or problem, not just "i want ....." – Melon Apr 04 '14 at 22:52
  • similar questions you can look at. http://stackoverflow.com/questions/9292313/create-a-new-table-from-merging-two-tables-with-union... http://stackoverflow.com/questions/5862178/create-mysql-table-from-multiple-mysql-tables-adding-together-identical-columns... – John Ruddell Apr 04 '14 at 23:02

1 Answers1

0

Try this: 

INSERT INTO table3 (SpeciesId, Latin)
SELECT A.SpeciesId, A.Latin FROM table1 A
INNER JOIN table2 B
ON A.Normal = B.Normal AND A.Latin = B.Latin AND
A.Category = B.Category AND A.Code = B.Code AND
A.CAAB - B.CAAB AND A.ITIS = B.ITIS;
jomsk1e
  • 3,585
  • 7
  • 34
  • 59
  • The code returns a 0. This is the exact code I then used which also returned 0. INSERT INTO table3 (SpeciesId, Latin) SELECT A.SpeciesId, A.Latin FROM species A INNER JOIN ASFIS2 B ON A.Normal = B.Normal AND A.Latin = B.Latin AND A.Category = B.Category AND A.Code = B.Code AND A.View = B.View AND A.CAAB - B.CAAB AND A.ITIS = B.ITIS; There are exact matches and different lines in table A – user3499879 Apr 05 '14 at 16:11
  • Thank you, I got this statement to work; INSERT INTO table3 (SpeciesId, Latin) SELECT A.SpeciesId, A.Latin FROM table1 A INNER JOIN table2 B ON A.Normal = B.Normal; but returns a 0 count when I use the AND term listing all 7 columns. – user3499879 Apr 07 '14 at 02:12