Find Values between two different columns in a table

Question

table one

+----------------------+
|column A | Column B|
|   2     |    4    | 
|   3     |    5    |
|   1     |    2    |
|   1     |    2    |
|   8     |    7    |
+----------------------+

Output

+-------+
|1 | 2  |
|1 | 2  |
+-------+

i want to print only the above output without COUNT, and any duplicate record example? please help

Hello showrya. Could you explain please the logic behind the output? All results that have the value 1 in Column A for example? — Anastasios Selmani, Nov 09 '18 at 11:18
You have no PRIMARY KEY, which is likely to prove problematic, either now or in the near future — Strawberry, Nov 09 '18 at 11:22
https://stackoverflow.com/questions/2300322/intersect-in-mysql — Super Mario, Nov 09 '18 at 12:58

Zaynul Abadin Tuhin · Answer 1 · 2018-11-09T11:23:47.823

0

how about below where cluase

   select * from t where columnA=1 and columnB=2

or

  select columnA,columnB from t
  group by columnA,columnB
  having count(*)>1

or you can use exists

select t1.* from t t1 where exists 
       (select 1 from t t2 where t2.columnA=t1.columnA
        and t2.columnB=t1.columnB group by columnA,columnB
        having count(*)>1
        )

edited Nov 09 '18 at 11:23

answered Nov 09 '18 at 11:14

Zaynul Abadin Tuhin

31,407
5
33
63

is this possible without COUNT – Nov 09 '18 at 11:18
@showrya yes two answer i provided so you can use upper btw your mysql version please – Zaynul Abadin Tuhin Nov 09 '18 at 11:19
@showrya you can use use corelated subquery my 3rd answer – Zaynul Abadin Tuhin Nov 09 '18 at 11:24

Madhur Bhaiya · Answer 2 · 2018-11-09T11:24:10.753

0

You possibly want only those rows which are duplicate. If you don't have Window Functions available in your MySQL version, you can do the following:

SELECT
  t.* 
FROM your_table AS t 
JOIN (SELECT columnA, columnB 
      FROM your_table 
      GROUP BY columnA, columnB 
      HAVING COUNT(*) > 1) AS dt 
  ON dt.columnA = t.columnA AND dt.columnB = t.columnB

Details: In a Derived table, we get all those combination of columnA and columnB which have more than one row(s) (HAVING COUNT(*) > 1).

Now, we simply join this result-set back to the main table, to get those rows only.

Note: This approach would not be needed if you want to fetch only these two columns. A simple Group By with Having would suffice, as suggested in other answer(s). However, if you have more columns in the table, and you will need to fetch all of them, and not just the columns (used to determine duplicates); you will need to use this approach.

edited Nov 09 '18 at 11:24

answered Nov 09 '18 at 11:14

Madhur Bhaiya

28,155
10
49
57

is it possible without COUNT – Nov 09 '18 at 11:17
No, I'm solving a another problem - The shorter form reduces to above question.Is there a solution possible by not using COUNT – Nov 09 '18 at 11:20
@showrya yes offcourse I could guess that. Please add more details to your question. I am pretty sure that you need to use Derived tables here, as you would have more columns in the table, and you will need to fetch all of them, not just the columns (used to determine) duplicates.. – Madhur Bhaiya Nov 09 '18 at 11:21
im working on a dB api(In a specific condition ) i need to display common values from two columns similar to above condition. It wouldbe really helpfull if i get result without using Count. – Nov 09 '18 at 11:27
@showrya What is the problem with using `Count()` ? please be more specific. Do you have any primary key in the table ? – Madhur Bhaiya Nov 09 '18 at 11:30
There's lot of similar files if i use count there will be other files. So is there a way to get the common values from two columns, So i could the same method for some other conditions. – Nov 09 '18 at 11:35
@showrya can you please edit the question to add one such "similar file" case to the problem. Without looking at some sample data, it is hard to figure out what you are asking for! – Madhur Bhaiya Nov 09 '18 at 11:38
@showrya I think I have already given an answer based on your current problem statement. – Madhur Bhaiya Nov 09 '18 at 11:58
Could you please eloborate maybe with the (conditions/joins) in the WHERE condition. – Nov 09 '18 at 12:01
@showrya Refer this link: https://dev.mysql.com/doc/refman/8.0/en/derived-tables.html This will clarify the usage of a subquery in the from clause – Madhur Bhaiya Nov 09 '18 at 12:08
Thank you for helping me, I'm a begginer in sql started a few days back.If you could provide me a solution for above question that would be really helpfull. I haven't studied the group or count. So can you provide a simple solution(for now) – Nov 09 '18 at 12:13

Barbaros Özhan · Answer 3 · 2018-11-09T11:37:08.793

0

You can use in operator with a grouped subquery as :

select *
  from tab
 where ( columnA, columnB) in
  (
    select columnA, count(columnA) 
      from tab   
     group by columnA  
   );

or use a self-join as :

select t1.columnA, t1.columnB
  from tab t1
  join 
  (
    select columnA, count(columnA) as columnB
      from tab   
     group by columnA  
   ) t2
    on ( t1.columnA = t2.columnA and t1.columnB = t2.columnB );

Rextester Demo

edited Nov 09 '18 at 11:37

answered Nov 09 '18 at 11:29

Barbaros Özhan

59,113
10
31
55

No, I'm solving a another problem - The shorter form reduces to above question.Is there a solution possible by not using COUNT or GROUPS – Nov 09 '18 at 12:06

score 0 · Answer 4 · answered Nov 09 '18 at 11:37

0

I would use EXISTS, if the table has primary column :

SELECT t.*
FROM table t
WHERE EXISTS (SELECT 1 FROM table t1 WHERE t1.col1 = t.col1 AND t1.col2 = t.col2 AND t1.pk <> t.pk);

answered Nov 09 '18 at 11:37

Yogesh Sharma

49,870
5
26
52

could you please eloborate, Im a beginner(regarding t, t1) – Nov 09 '18 at 12:05
@showrya. . . `t` is outer query table reference & `t1` is inner query table reference. – Yogesh Sharma Nov 12 '18 at 14:49

Find Values between two different columns in a table

4 Answers4