adplus-dvertising

SQL to pull duplicated data from one column or another

Asked 2 days ago
Viewed 21 times

I've been struggling with creating an SQL script in Access that will pull some columns from a table if the data is duplicated in one column or another. My example table looks like this:

title MAC Online time
PC1 ab-bc-cd-ef-12 Online Today
PC2 ab-bc-cd-ef-12 Offline Yesterday
PC1 ab-bc-89-c5-78 Offline 1 year
PC4 a6-65-bf-33-01 Online Today

and I'm trying to get an output similar to:

title MAC Online time
PC1 ab-bc-cd-ef-12 Online Today
PC2 ab-bc-cd-ef-12 Offline Yesterday
PC1 ab-bc-89-c5-78 Offline 1 year

I have put together this SQL so far:

select
    s.title,
    MAC,
    time,
    online
FROM
    Table1 AS s
    INNER JOIN ( SELECT title FROM Table1 GROUP BY title HAVING count(*) > 1) AS t
    INNER JOIN ( SELECT MAC FROM Table1 GROUP BY MAC HAVING count(*) > 1) AS u ON
        s.title = t.title AND s.MAC = u.MAC

Running it however bring up there is a Syntax error in the FROM clause. I have tried adding the parenthesis into the inital FROM statement like:

select
    s.title,
    MAC,
    time,
    online
FROM
    (
        (
            Table1 AS s
            INNER JOIN ( SELECT title FROM Table1 GROUP BY title HAVING count(*) > 1 ) AS t
        )
        INNER JOIN
        (
            SELECT MAC FROM Table1 GROUP BY MAC HAVING count(*) > 1
        ) AS u 
            ON s.title = t.title AND s.MAC = u.MAC

Anywhere I add further parenthesis will highlight it as the error when I try to run it, but removing them highlights the second JOIN statement as the error.

I'm sure there are probably other errors in the code or even a better way to write this. Any help is greatly appreciated.

asked 2 days ago

Correct Answer

Try this union query.

SELECT tblDuplicate.Title, tblDuplicate.MAC, tblDuplicate.Online, tblDuplicate.OnlineTime FROM tblDuplicate INNER JOIN (SELECT tblDuplicate.Title, Count(tblDuplicate.Title) AS CountOfTitle FROM tblDuplicate GROUP BY tblDuplicate.Title HAVING (((Count(tblDuplicate.Title))>1))) as t1 ON tblDuplicate.Title = t1.Title;
    union
SELECT tblDuplicate.Title, tblDuplicate.MAC, tblDuplicate.Online, tblDuplicate.OnlineTime FROM tblDuplicate INNER JOIN (SELECT tblDuplicate.MAC, Count(tblDuplicate.MAC) AS CountOfMAC FROM tblDuplicate GROUP BY tblDuplicate.MAC HAVING (((Count(tblDuplicate.MAC))>1))) as t2 ON tblDuplicate.MAC = t2.MAC;
answered 2 days ago