left outher join

[minombreesmm]

no me quiere funcionar la consulta

quiero que me muestre los registros de la tabla usuarios


usuarios tiene 11 registros
amigos tiene 6 registros.

quiero que la consulta left outher join me muestre los 11 registro de usuarios y que me muestre como null los que no estan en la tabla amigos.


tomando en cuenta esto.


tabla usuarios

Código tabla usuarios:

id
nombre
apellido
 

tabla amigos

Código tabla amigos:

ida
idb
estadosolicitud
 

el detalle es que el usuarios.id puede estar en amigos.ida como en amigos.idb
y no deben repetirse solo se deben mostrar los registros de la tabla usuarios yen null los que no están en la tabla amigos


sin embargo se repiten




esto contiene la tabla amigos (6 registros)
ida , idb, estadosolicitud
322, 325, 'ACEPTADA'
317, 323, 'YES'
317, 315, 'YES'
315, 320, 'YES'
322, 315, 'ACEPTADA'
315, 316, 'YES'




y esto la usuarios (11 registros)

id , nombreyapellido
321, 'g'
319, 'a'
320, 'b'
317, 'c'
318, 'd'
315, 'e'
316, 'f'
322, 'g'
323, 'h'
324, 'i'
325, 'j'



quiero que la consulta me arroje esto(11 registros)


321, 'g',NULL
319, 'a',NULL
320, 'b'
317, 'c'
318, 'd',NUL
315, 'e'
316, 'f'
322, 'g'
323, 'h'
324, 'i',NULL
325, 'j'


OSEA solamente debe aparecerme null los que no estan.

y donde batallo es que el usuarios.id puede estar en amigos.ida o amigos.idb
y puede hacer que los datos se repitan

hice esto

Código MySQL:

select usuarios.id,amigos.idusuario,amigos.idamigo
from usuarios
LEFT OUTER JOIN amigos ON
(usuarios.id=amigos.idusuario) or (usuarios.id=amigos.idamigo)
and amigos.idusuario=315 and amigos.idamigo=315
where usuarios.id<>315
order by usuarios.id;
 

y este es el resultado, con los mismos datos de arriba
usuarios.id,amigos.ida,amigos.idb
316, null, null
317, 317, 323
317, 317, 315
318, null, null
319,null , null
320, null, null
321,null , null
322, 322, 325
322, 322, 315
323, null, null
324, null, null
325, null, null

(me regresa 12 registros, y solo deben ser 11.
alguna sugerencia?

mi problema radica en que se puede repetir el ida tantas relaciones tenga con idb y biceversa se repite el idb tantas relaciones tenga con el ida.
entonces como se pueden mostrar en null los registros de usuarios que no esten en la tabla amigos si la tabla amigos es posible que se repita muchas veces el id.



de antemano gracias

[quimfv]

quiero que la consulta me arroje esto(11 registros)


321, 'g',NULL
319, 'a',NULL
320, 'b'
317, 'c'
318, 'd',NUL
315, 'e'
316, 'f'
322, 'g'
323, 'h'
324, 'i',NULL
325, 'j'

No se entiende para que quieres ese resueltado, puesto que es una lista de los usuarios, con null si no tienen amigos y nada sitienen amigos...???

Para obtener eso o algo parecido debes agregar... es decir tu no buscas la lista de "amistades" donde obvimente tendras mas registros que usuarios si tienes usuarios con mas de un amigo... sino una lista de usuarios con información de si tienen o no amigos...

Código MySQL:

CREATE TABLE usuarios 
    (
     id INT NOT NULL PRIMARY KEY,
     nombre varchar(20) 
    );
 
 
INSERT INTO usuarios 
(id,nombre)
VALUES
(321, 'g'),
(319, 'a'),
(320, 'b'),
(317, 'c'),
(318, 'd'),
(315, 'e'),
(316, 'f'),
(322, 'g'),
(323, 'h'),
(324, 'i'),
(325, 'j');
 
CREATE TABLE amigos 
    (
     idusuario INT,
     idamigo INT,
     estadosolicitud varchar(20) 
    );
 
INSERT INTO amigos
(idusuario,idamigo,estadosolicitud)
VALUES
(322, 325, 'ACEPTADA'),
(317, 323, 'YES'),
(317, 315, 'YES'),
(315, 320, 'YES'),
(322, 315, 'ACEPTADA'),
(315, 316, 'YES');
 

Código MySQL:

SELECT usuarios.id,usuarios.nombre,
SUM(if(amigos.idusuario IS NULL,0,1)) amistades
FROM usuarios
LEFT OUTER JOIN amigos ON 
(usuarios.id=amigos.idusuario) 
OR (usuarios.id=amigos.idamigo)
GROUP BY usuarios.id,usuarios.nombre
ORDER BY usuarios.id;
 

Código pre:

ID     NOMBRE     AMISTADES
315     e     4
316     f     1
317     c     2
318     d     0
319     a     0
320     b     1
321     g     0
322     g     2
323     h     1
324     i     0
325     j     1
 

http://sqlfiddle.com/#!2/28563/5/0

[minombreesmm]

Iniciado por quimfv

No se entiende para que quieres ese resueltado, puesto que es una lista de los usuarios, con null si no tienen amigos y nada sitienen amigos...???

Para obtener eso o algo parecido debes agregar... es decir tu no buscas la lista de "amistades" donde obvimente tendras mas registros que usuarios si tienes usuarios con mas de un amigo... sino una lista de usuarios con información de si tienen o no amigos...



http://sqlfiddle.com/#!2/28563/5/0

creo que no me explique bien

lo que yo busco es las lista de usuarios, y que dependiendo el usuario logeado pueda ver si los usuarios que ve en pantalla son amigos de el, si les envio solicitud o si le enviaron o solamente son usuarios desconocidos.
4 cosas..
me muestras una tabla con campo amistad entiendo que esa es el estadosolicitud.
analizare bien lo que me enviaste para entenderlo

osea si la tabla usuario tiene 11 registros y la tabla amigos tiene 5 registros.
solo me debe mostrar 10 registros.
11-1 por que no debe verse a si mismo el usuario logeado.
pero si debe haber la relacion que tiene con los usuarios.

[quimfv]

Código MySQL:

SELECT u.id,
       u.nombre,
       a.estadosolicitud,
       if(a.idusuario IS NULL, "Desconocido","Amigo")conocido 
FROM usuarios u 
LEFT JOIN 
(SELECT idusuario,idamigo,estadosolicitud FROM amigos
 WHERE idusuario=315 or idamigo=315) a
ON (u.id=a.idusuario) 
OR (u.id=a.idamigo)
WHERE u.id<>315
 

Para el usuario 315

[minombreesmm]

Iniciado por quimfv

Código MySQL:

SELECT u.id,
       u.nombre,
       a.estadosolicitud,
       if(a.idusuario IS NULL, "Desconocido","Amigo")conocido 
FROM usuarios u 
LEFT JOIN 
(SELECT idusuario,idamigo,estadosolicitud FROM amigos
 WHERE idusuario=315 or idamigo=315) a
ON (u.id=a.idusuario) 
OR (u.id=a.idamigo)
WHERE u.id<>315
 

Para el usuario 315

muchisimas gracias me sirvio mucho, wow no puedo creerlo es como si hicieras un select * from tabla;
no dura nada la consulta solo le pongo limit 20 y de volada oO wow

tratare de entenderla, ya que es demasiado para mi por ahora.
no puedo creerlo la mia era tan lenta, que con 500 usuarios duraba mucho.
y esta la probe con 6 millones y no hay diferencia de tiempo, dura lo mismo por que es el mismo limit wow.
bien podria tener 80 mil millones y no abria diferencia.
ahora en lo que batallo es en el filtro de busqueda.
ya le puse nombre like '%".$filtro."%'
pero cuando quiero que tambien busque de apellido me arroja registros demas..

lo que yo queria es que solo buscara por cualquiera de los dos al poner el nombre o apellido en el cuadro de busqueda.
cuando nombre like '%".$filtro."%' or apellido like '%".$filtro."%'
si pongo eso me manda registros de mas de coincidencia.
si pongo and, no me manda nada por logicas razones. entonces como le podria poner.
ahora entiendo como es que facebook hace las busquedas tan eficientes jej
y con ajax imagino todavia que sera mas rapido, puesto que al borrar un amigo aparte de que no es usual solo se usara ajax.
eso del boton me gusta estara mas complicado creo
gracias




aparte de eso, inserte 30 millones de registros, todo funciona rapido excepto la busqueda, cuando busco algo que si esta, lo hace rapido pero cuando busco algo que no esta se queda trabada, ya estoy añadiendole indices, espero que de una hora de busqueda cuando algo no esta ahora solo dure un segundo y me diga que no esta. por cierto uso el like