SQL intersect two joined tables

Question

I joined two tables

SELECT 
    t1.column1, 
    t2.column2  
FROM 
    t1 JOIN t2 cu 
        ON t1.id = t2.id AND t1.col LIKE 'A%'  



SELECT
    t1.column1, 
    t2.column2  
FROM
    t1 JOIN t2 cu 
        ON t1.id = t2.id AND t1.col LIKE 'B%'

How could i intersect these tables so I can have the following output (ids that have both the A% and B%)

join 1

id | col
---┼------
1  | Axxxx
2  | Axxxx

join 2

id | col
---┼-------
1  | Bxxxx
3  | Bxxxx

Final output

id | col
---┼-------
1  | Axxxx
1  | Bxxxx

Thank you

Could you please give a small example/template, HoneyBadger, I'm a beginner in SQL :( — gabi906, Jan 12 '16 at 10:18
You want the rows from both the tables having the same id? Mandatory with intersect or is the result your goal..? — ScaisEdge, Jan 12 '16 at 10:23
the table are within the same database and basicaly yes, ids that have col both like A% and B% — gabi906, Jan 12 '16 at 10:24
What DBMS are you using? SQL Server, Oracle, PostgreSQL, MySQL, ...? — TT., Jan 12 '16 at 10:25
The DB is SQL and they have the same id because there were 2 tables with id as a fk that were joined. Now I have to intersect them and get the ids that have col both as A% and B% — gabi906, Jan 12 '16 at 10:25
SQL is a language not a DBMS. What product (e.g. MySQL or SQL Server or Oracle) are you using? — Thorsten Kettner, Jan 12 '16 at 10:29
Please update your example to make it more plausible. An ID uniquely identifies a record in a table. It is highly unlikely to have two tables the IDs of which are related (as in `ON t1.id = t2.id`). Also please show queries that match your sample data (`id` vs. `column1`). — Thorsten Kettner, Jan 12 '16 at 10:32

score 2 · Accepted Answer · answered Jan 12 '16 at 10:29

If I understood correctly, and what you want is only the ID's that have both A% and B%, then this is your answer

SELECT 
t1.column1, 
t2.column2  
FROM 
t1 JOIN t2 cu 
    ON t1.id = t2.id AND ((t1.col LIKE 'A%'  AND t2.col like 'B%')
    OR (t1.col LIKE 'B%' AND t2.col like 'A%'))

score 1 · Answer 2 · answered Jan 12 '16 at 10:28

Based on your sample you don't need intersect but a simple union

 SELECT 
  t1.column1, 
  t2.column2  
FROM 
  t1 JOIN t2 cu 
      ON t1.id = t2.id AND t1.col LIKE 'A%'  

union

SELECT
  t1.column1, 
  t2.column2  
FROM
  t1 JOIN t2 cu 
      ON t1.id = t2.id AND t1.col LIKE 'B%'

adapt the sintax for union at your db/sql

score 1 · Answer 3 · answered Jan 12 '16 at 10:47

Also try it this way ... as you are looking for only repeatable IDs ... you could just count them from inner select

select column1, column2 from (
     select column1, column2, count(column1) over (partition by column1) [Counted] from (
        SELECT 
           t1.column1, 
           t2.column2  
        FROM 
        t1 JOIN t2 cu 
            ON t1.id = t2.id AND t1.col LIKE 'A%'
     UNION
        SELECT
           t1.column1, 
           t2.column2  
        FROM
           t1 JOIN t2 cu 
           ON t1.id = t2.id AND t1.col LIKE 'B%' 
    ) src
) src2 where Counted > 1

SQL intersect two joined tables

3 Answers3