How to show only one duplicate?
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 widge开发者_JAVA技巧ts 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?
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
精彩评论