SQL cómo buscar una relación de muchos a muchos

search many-to-many

Question

Tengo una base de datos con dos tablas principales, notes y labels . Tienen una relación de muchos a muchos (similar a como stackoverflow.com tiene preguntas con las etiquetas). Lo que me pregunto es ¿cómo puedo buscar una nota usando múltiples etiquetas usando SQL?

Por ejemplo, si tengo una nota "prueba" con tres etiquetas "uno", "dos" y "tres" y tengo una segunda nota "prueba2" con las etiquetas "uno" y "dos", ¿cuál es la consulta SQL que encontrar todas las notas que están asociadas con las etiquetas "uno" y "dos"?

Answer 1

Algo como esto ... (necesitarás otra tabla de enlaces)

SELECT *
FROM Notes n INNER JOIN NoteLabels nl
ON n.noteId = nl.noteId
WHERE nl.labelId in (1, 2)

Editar : la tabla NoteLabel tendrá dos columnas, noteId y labelId, con una PK compuesta.

Answer 2

No dice nada acerca de cómo se realiza esta relación de muchos a muchos. Supongo que la tabla de etiquetas tiene es Etiquetas (noteid: int, label: varchar) - con una clave principal que abarca ambos?

SELECT DISTINCT n.id from notes as n, notes_labels as nl WHERE n.id = nl.noteid AND nl.text in (label1, label2);

Reemplace con los nombres de sus columnas e inserte los marcadores de posición adecuados para las etiquetas.

Answer 3

Nota: en realidad no he probado esto. También supone que tiene una tabla de muchos a muchos llamada notes_labels, que puede no ser el caso en absoluto.

Si solo quieres las notas que tengan alguna de las etiquetas, será algo como esto

SELECT DISTINCT n.id, n.text
FROM notes n
INNER JOIN notes_labels nl ON n.id = nl.note_id
INNER JOIN labels l ON nl.label_id = l.id
WHERE l.label IN (?, ?)

Si quieres las notas que tienen TODAS las etiquetas, hay un poco de trabajo extra

SELECT n.id, n.text
FROM notes n
INNER JOIN notes_labels nl ON n.id = nl.note_id
INNER JOIN labels l ON nl.label_id = l.id
WHERE l.label IN (?, ?)
GROUP BY n.id, n.text
HAVING COUNT(*) = 2;

? ser un marcador de posición SQL y 2 ser el número de etiquetas que estaba buscando. Esto supone que la tabla de enlace tiene ambas columnas de ID como clave primaria compuesta.

Answer 4

Para obtener los detalles de las notas que tienen ambas etiquetas ''Uno'' y ''Dos'':

select * from notes
where note_id in
( select note_id from labels where label = ''One''
  intersect
  select note_id from labels where label = ''Two''
)

Answer 5

Si solo necesita una lista, puede usarla where exists para evitar la duplicación. Si tiene varias etiquetas contra un nodo en sus criterios de selección, obtendrá filas duplicadas en el resultado. Aquí hay un ejemplo de where exists :

create table notes (
       NoteID int not null primary key
      ,NoteText varchar (max)
)
go

create table tags (
       TagID int not null primary key
      ,TagText varchar (100)
)
go

create table note_tag (
       NoteID int not null
      ,TagID int not null
)
go

alter table note_tag
  add constraint PK_NoteTag
      primary key clustered (TagID, NoteID)
go

insert notes values (1, ''Note A'')
insert notes values (2, ''Note B'')
insert notes values (3, ''Note C'')

insert tags values (1, ''Tag1'')
insert tags values (2, ''Tag2'')
insert tags values (3, ''Tag3'')

insert note_tag values (1, 1) -- Note A, Tag1
insert note_tag values (1, 2) -- Note A, Tag2
insert note_tag values (2, 2) -- Note B, Tag2
insert note_tag values (3, 1) -- Note C, Tag1
insert note_tag values (3, 3) -- Note C, Tag3
go

select n.NoteID
      ,n.NoteText
  from notes n
 where exists
       (select 1
          from note_tag nt
          join tags t
            on t.TagID = nt.TagID
         where n.NoteID = nt.NoteID
           and t.TagText in (''Tag1'', ''Tag3''))


NoteID      NoteText
----------- ----------------
1           Note A
3           Note C

Answer 6

Suponiendo que tiene una base de datos normalizada, debe tener otra tabla entre notes y labels

A continuación, debe usar una inner join para unir las tablas

1. Únase a la tabla de labels con la tabla de vinculación (tabla de muchos a muchos)
2. Únete a la tabla de notes con la consulta anterior

Ejemplo:

select * from ((labels l inner join labels_notes ln on l.labelid = ln.labelid) inner join notes n on ln.notesid = n.noteid)

De esa forma, has conectado ambas tablas juntas.

Ahora lo que necesitas agregar es la cláusula where ... pero te lo dejo a ti.

Answer 7

select * from notes a
inner join notes_labels mm on (mm.note = a.id and mm.labeltext in (''one'', ''two'') )

Por supuesto, reemplace con sus nombres de columna reales, con suerte mis suposiciones acerca de su tabla eran correctas.

Y en realidad hay un poco de ambigüedad posible en su pregunta gracias al inglés y cómo la palabra ''y'' a veces se usa. Si quiere decir que quiere ver, por ejemplo, una nota etiquetada como ''uno'' pero no ''dos'', esto debería funcionar (interpretar su ''y'' para significar ''mostrarme todas las notas con la etiqueta'' uno ''y / más todas las notas con la etiqueta ''dos''). Sin embargo, si solo quieres notas que tengan ambas etiquetas, esta sería una forma de hacerlo:

select * from notes a
where exists (select 1 from notes_labels b where b.note = a.id and b.labeltext = ''one'')
     and exists (select 1 from notes_labels c where c.note = a.id and c.labeltext = ''two'')

Edición: gracias por las sugerencias de todos, los cambios del lunes en mi cerebro son un poco lentos ... ¡parece que debería haberlo hecho!