Jump to content


Photo

Join Two Tables


  • Please log in to reply
2 replies to this topic

#1 nabeel21

nabeel21
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 20 October 2003 - 12:12 AM

I have two tables:
1. tb1
2. tb2


They share the same column called \"County\". I want to select all entries from tb1 that are NOT in tb2.

E.g. If a county \"xyz\" is in tb2 then i dont want it to show up when i run the query on tb1.

#2 bigdoggy59

bigdoggy59
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 20 October 2003 - 06:56 AM

I took this right out of the MySQL Manual. Hope it helps.

If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:

mysql> select table1.* from table1           LEFT JOIN table2 ON table1.id=table2.id           where table2.id is NULL;

This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL, of course. See section 12.5.5 How MySQL Optimizes LEFT JOIN and RIGHT JOIN.

#3 nabeel21

nabeel21
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 20 October 2003 - 02:12 PM

Here are 2 tables which share the same columns (CITY and COUNTY)

TB1 (Main table that stores the info of all cities and counties)

CITY-------------------------COUNTY
---------------------------------------
Tampa----------------------Hillsborough
Orlando---------------------Orange
Frostproof-------------------Polk
Arcadia----------------------Desoto
County----------------------Collier
Tallahassee-----------------Leon
----------------------------------------------

TB2 (Contains info for all cities and counties who have submitted data)

CITY--------------------------COUNTY
---------------------------------------
Arcadia----------------------Desoto
County----------------------Collier
Tallahassee-----------------Leon
------------------------------------------------

Now I want to query TB1 and find out which city/county has NOT submitted their data.

The query should return

CITY-------------------------COUNTY
----------------------------------------
Tampa----------------------Hillsborough
Orlando---------------------Orange
Frostproof------------------Polk


Thanks in advance.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users