How to show only one duplicate?

Question

This is kind of an extension of this question of mysql not in or value=0?

I have two databases one has the widget information and the other is the layout of those widgets according to the module. On the page I have lists to put these widget such as Header, Content, Sidebar, and Footer. There is also a list of widget that are not being used. That is where I am having the problem.

Here is the mysql that I am using:

SELECT *
FROM widgets AS w
    LEFT JOIN widget_layouts AS wl
        ON w.id = wl.widget_id
WHERE wl.widget_id IS NULL
    OR wl.module_id <> '2'

This displays all of the widget that don't have module_id of 2. So if I put one of the widgets into another list it will still show up in the unused widget list because it is also being used by another widget.

Here is the table of widget_layouts:

|Module_id | Widget_id | Position | Weight|
-------------------------------------------
|2         |3          |1         |0      |
|1         |9          |3         |3      |
|1         |8          |3         |2      |
|1         |3          |2         |1      |

So here is the question:

How do I display a list of widgets that are not being used by module 2 or don't exist?

EDIT:

I am going to try to clear up of what I am trying to do. I want a list to show the widgets that are not in the list and widgets not being used by module 2 but if it is being used by module 2 then don't display that widget.

I have two lists one for content and one that displays available widgets. So when I drag widget 3 from the widget list into the content list. Then to a browser refresh there are two widget 3 that are displayed, one in the content list and one in the available list.

So how do I show widgets that are not in the database and if the widget is used by module 2 it does not show up in the available list?

Answer 1

You can group columns with the same values with SQL:

SELECT widget_id
FROM widgets AS w
    LEFT JOIN widget_layouts AS wl
        ON w.id = wl.widget_id
WHERE wl.widget_id IS NULL
    OR wl.module_id <> '2'
    GROUP BY widget_id