CODEWITHSUNDEEP
mysqlsql
Ram Chander
Ram Chander

Reputation: 1522

How to fetch data ( without duplicates records ) from two different tables ? where both tables have common value

Table 1

newpancard

id | name | cardno | status |
-----------------------------
1  | name1| 909099 | done   |
2  | name2| 800099 | done   |
3  | name3| 965099 | pending|

Table 2

oldpancard

id | name | cardno | status |
-----------------------------
1  | name4| 111119 | done   |
2  | name5| 323239 | done   |
3  | name6| 734349 | pending|
4  | name7| 609099 | done   |

can we get fetch data from both tables where status = done in both tables?

I am trying the following query but getting duplicates data in bulk.

SELECT tb1.*, tb2.* 
FROM `newpancard` tb1 
JOIN `oldpancard` tb2 
  ON tb1.status = tb2.status

please correct me. Thanks

Upvotes: 3

Views: 272

Answers (1)

Nick
Nick

Reputation: 147166

I think you actually want a UNION:

SELECT * FROM newpancard WHERE status='done'
UNION
SELECT * FROM oldpancard WHERE status='done'

We use a UNION (rather than UNION ALL) so we don't get duplicate records from newpancard and oldpancard

Output (from your sample data):

id  name    cardno  status
1   name1   909099  done
2   name2   800099  done
1   name4   111119  done
2   name5   323239  done
4   name7   609099  done

SQLFiddle

Upvotes: 5

Related Questions