Remove duplicate records from multi-table queries

Remove duplicate records from multi-table queries

First, we can think of using group by or distinct to remove duplicate records in SQL multi-table queries, and then think of inner left, etc,
Let's take a look at an instance.

View a distinct instance

Now release the complete statement:

Select *, count (distinct name) from table group by name

Result:

Id name count (distinct name)
1 a 1
2 B 1
3 c 1

The last item is redundant, so you don't have to worry about it. The goal is achieved .....

Group by must be placed before order by and limit. Otherwise, an error will be reported.

Db_a:
Id age
1 20
2 30
3 40
4 50
Db_ B:
Topid poto
2 axxxxxxxxxx
2 bxxxxxxxxxx
2 cxxxxxxxxxxx
3 dxxxxxxxxxxx

SELECT * FROM db_a as a left join db_ B as B on B. topid = A. id;

Now we have 6 data records. How can we solve this problem.

SELECT * FROM db_a as a right join db_ B as B on B. topid = A. id;
// Four data entries. Do you want it?
Id age topicid poto
2 bbbbbb 2 axxxxx
2 bbbbbb 2 bxxxxxx
2 bbbbbb 2 cxxxxx
3 cccccc 3 dxxxxxx

SELECT * FROM db_a as a, db_ B as B where B. topid = A. id

Select distinct (column name) from table
Find out this table. The values in this column are not repeated.
Distinct (column name)

SELECT * FROM db_a as a inner join db_ B as B on a. id = B. topid;

SELECT * FROM db_a as a left JOIN db_ B as B on a. id = B. topid goup by a. id;

More Methods

Method 1: use union

Select a. menuId, menuAliasNumber, menuName1, menuName2,
(Select price from workmenuPrice where workmenuPrice. menuId = a. menuId and menuPriceTypeId = 1) as 'reg ',
(Select price from workmenuPrice where workmenuPrice. menuId = a. menuId and menuPriceTypeId = 2) as 'large ',
(Select price from workmenuPrice where workmenuPrice. menuId = a. menuId and menuPriceTypeId = 3) as 'small'
From workmenuItems a right join workmenuCatUse B on a. menuId = B. menuId
Right join workmenuPrice c on c. menuId = B. menuId
Union
Select a. menuId, menuAliasNumber, menuName1, menuName2,
(Select price from workmenuPrice where workmenuPrice. menuId = a. menuId and menuPriceTypeId = 1) as 'reg ',
(Select price from workmenuPrice where workmenuPrice. menuId = a. menuId and menuPriceTypeId = 2) as 'large ',
(Select price from workmenuPrice where workmenuPrice. menuId = a. menuId and menuPriceTypeId = 3) as 'small'
From workmenuItems a right join workmenuCatUse B on a. menuId = B. menuId
Right join workmenuPrice c on c. menuId = B. menuId

Method 2: Use distinct

Select distinct (a. menuId), menuAliasNumber, menuName1, menuName2,
(Select price from workmenuPrice where workmenuPrice. menuId = a. menuId and menuPriceTypeId = 1) as 'reg ',
(Select price from workmenuPrice where workmenuPrice. menuId = a. menuId and menuPriceTypeId = 2) as 'large ',
(Select price from workmenuPrice where workmenuPrice. menuId = a. menuId and menuPriceTypeId = 3) as 'small'
From workmenuItems a right join workmenuCatUse B on a. menuId = B. menuId
Right join workmenuPrice c on c. menuId = B. menuId