Question 238263 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Display the entire row in the SELECT output

I am a beginner in SQL, this is maybe a dumb question…

Table 1 (guide_book) :

ID Table ID Column A
0 0 a
1 0 b
2 0 c
3 0 d

Table 2 (adding) :

ID Table ID Column A
0 1 b
1 1 c
2 1 d
3 1 e

Query

SELECT Column A 
FROM (
  SELECT ID, Table ID, Column A 
  FROM guide_book 
  UNION ALL 
  SELECT ID, Table ID, Column A 
  FROM adding
) temp
GROUP BY Column A 
having count(*)=1;

Output

Column A
a
e

Desired output

ID Table ID Column A
0 0 a
3 1 e

Since I have big table I need to know where are the results and in which table.

Thank you

Tags:

2

Answers


  1. 0

    You can make use of a windowed count here:

    select ID, TableID, ColumnA
from (
    select *, Count(*) over(partition by columnA) Acnt
    from (
        select ID, TableID, ColumnA 
        from t1 
        union all 
        select ID, TableID, ColumnA 
        from t2
    )t
)t
where Acnt = 1;
    Login or Signup to reply.
  2. 0

    Your query groups by Column A and filters out unique values, but it doesn’t keep track of the original ID and Table ID. To get the desired output, you can use Common Table Expressions (CTEs).

    First, create a CTE named Combined that unions both tables (guide_book and adding). Then, create another CTE named Counts that counts the occurrences of each value in Column A.

    Finally, perform a SELECT query that joins the Combined and Counts CTEs. Filter the rows where the count is 1 to get the unique values along with their ID and Table ID.

    Here’s an example:

    WITH Combined AS (
  SELECT ID, "Table ID", "Column A" FROM guide_book
  UNION ALL
  SELECT ID, "Table ID", "Column A" FROM adding
),
Counts AS (
  SELECT "Column A", COUNT(*) AS cnt FROM Combined
  GROUP BY "Column A"
)
SELECT c.ID, c."Table ID", c."Column A"
FROM Combined c
JOIN Counts ct ON c."Column A" = ct."Column A"
WHERE ct.cnt = 1;

    This should give you the desired output with ID, Table ID, and Column A for the unique values.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search