Help with SQL Query



  • Hi!

    I'm facing an issue with a SQL query and I'm not able to create the query that I want (I can't figure it out). I need the query to do this:

    It needs to select * from the table "usuarios" where the "Foto" and "FotoHuella" are null values and (here comes the problems) the user has to be able to filter the search so I have a few QComboBox and QLineEdits so the query should be like:

    query.prepare("SELECT * FROM usuarios WHERE Foto IS NULL AND FotoHuella IS NULL AND Curso=:curso "
                      "AND Grupo=:grupo AND Nombre=:nombre AND Apellido1=:apellido1 AND Apellido2=:apellido2");
    

    But it doesn't work because the filtering doesn't work (it always displays all the rows where "Foto=NULL" so ALL the rows are displayed.

    Thanks in advance.


  • Lifetime Qt Champion

    Hi,

    IIRC binding values are only available for INSERT query and stored procedure.

    You should build your SELECT query e.g. QString's arg function.



  • @SGaist No I think you're wrong I used binding values with select a lot of times in my code, that's not the problem. The problem is the query doesn't filter as I want to be.

    Thanks in advance.


  • Lifetime Qt Champion

    Good, I'll fix my memory then :)

    Did you check what lastQuery returns ?

    How are you using that query ?



  • @SGaist Query last is true so I think the problem is the query itself. I mean I don't know how to SELECT the table as I want, I need to SELECT from the table "usuarios" where the fields "Foto" and "FotoUser" are null but then I have to be able to filter it so I set "Nombre=:nombre1" OR "Curso=:curso1" but that ending statement makes the "WHERE NULL" unuseful because every row that has the binding values as I want will be displayed. Not only the ones with NULL values but ALL the rows



  • @cxam said:

    but then I have to be able to filter it so I set "Nombre=:nombre1" OR "Curso=:curso1"

    In the top post, you have AND between each term, but here you say OR. OR would explain the issue.

    If you're using OR, you probably want some braces, like:

    SELECT * FROM usuarios WHERE Foto IS NULL AND FotoHuella IS NULL
    AND (Curso=:curso OR Grupo=:grupo OR Nombre=:nombre OR Apellido1=:apellido1 OR Apellido2=:apellido2)
    

  • Lifetime Qt Champion

    lastQuery should return the actually query that ran against the database, that one would be interesting to see.

    Good point of @paul-colby


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.