Do not show certain records in an sql query

Question

Do not show certain records in an sql query

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I am making a system of online courses, and in the list of all courses offered to the public I would like to ignore what the user in session has already purchased.

I have two tables that are:

Courses:

|-----------|---------------|------------|
| idcurso   | nombre        | descripcion| 
|-----------|---------------|------------|
| 2547      | Html 5 + Css3 |   texto    | 
|-----------|---------------|------------|
| 2555      | Javascript    |   texto    | 
|-----------|---------------|------------|
| 2568      | PHP           |   texto    | 
|-----------|---------------|------------|

Another table called contest users , where courses acquired by users are stored

|------------------|--------|------------|-------------------|
| id_usuarioxcurso | iduser | idcurso    | fecha_inscripcion |
|------------------|--------|------------|-------------------|
| 1                |   1    |   2547     |    2018-04-07     |
|------------------|--------|------------|-------------------|
| 2                |   2    |   2547     |    2018-04-07     |
|------------------|--------|------------|-------------------|

The query itself, assuming that the user id in session is 1 , should show the Javascript and PHP courses. Previously I made a join of the two tables that is as follows:

SELECT  c.idcurso, c.nombre_curso, c.descripcion, u.iduser, u.idcurso FROM cursos c LEFT JOIN usuarioxcursos u ON c.idcurso=u.idcurso

With this sentence I get all of them, but the error that I have, even though with php I managed to remove the user's session password, shows me those of the other users. Example: If he user 1, user 2, user 3, have the same course purchased in the results would have repeated repeated course 2 times. (Note: in the initial consultation 3 times the course would be repeated) My wish is that if user 1 has acquired the course of Html 5 + css3, in the results you have:

|-----------|---------------|------------|
| idcurso   | nombre        | descripcion| 
|-----------|---------------|------------|
| 2555      | Javascript    |   texto    | 
|-----------|---------------|------------|
| 2568      | PHP           |   texto    | 
|-----------|---------------|------------|

If someone could help me, thanks in advance

mysql sql php

asked by Christina 09.05.2018 в 05:47

source

2 answers

0

The problem is that you are not filtering the data for the current user, that is, you need a where in the query something like this:

Where idusuario = $usuarioactual

I hope it helps and solves your problem

answered by 09.05.2018 в 07:39

How can I consult several elements of a database with the same identifier? -SQLite-Android Studio Change the date the bootstrap-material-datetimepicker calendar returns me

score 0 · Accepted Answer

You could use the NOT IN clause to discard those courses from a user that are in the usuarioxcursos table.

Doing something like this:

SELECT  
  c.idcurso, 
  c.nombre_curso, 
  c.descripcion
FROM cursos c 
WHERE c.idcurso NOT IN 
(
  SELECT u.idcurso FROM usuarioxcursos u WHERE u.iduser=1
);

A complete example for testing you can see here

CREATE TABLE cursos (
  idcurso INT,
  nombre_curso text,
  descripcion text
);

CREATE TABLE usuarioxcursos (
  id INT,
  iduser INT,
  idcurso INT,
  fecha_inscripcion DATE
);

INSERT INTO cursos (idcurso, nombre_curso, descripcion) VALUES 
(2547, 'html', 'esto es html'),
(2568, 'php', 'esto es php'),
(2555, 'JS', 'esto es js');

INSERT INTO usuarioxcursos (id, iduser, idcurso, fecha_inscripcion) VALUES 
(1, 1, 2547, '2018-04-07'),
(2, 2, 2547, '2018-04-07');


SELECT * FROM usuarioxcursos;

SELECT  
  c.idcurso, 
  c.nombre_curso, 
  c.descripcion
FROM cursos c 
WHERE c.idcurso NOT IN 
(
  SELECT u.idcurso FROM usuarioxcursos u WHERE u.iduser=1
);

Result:

idcurso | nombre_curso | descripcion
------------------------------------
2568    | php          | esto es php
2555    | JS           | esto es js