Jump to content

Archived

This topic is now archived and is closed to further replies.

nabeel21

Join Two Tables

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.