Qt Forum

    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Unsolved

    Unsolved Help with SQL Query

    General and Desktop
    query sql qsql
    3
    7
    2029
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • cxam
      cxam last edited by

      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.

      Stay Hungry, Stay Foolish

      1 Reply Last reply Reply Quote 0
      • SGaist
        SGaist Lifetime Qt Champion last edited by

        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.

        Interested in AI ? www.idiap.ch
        Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

        cxam 1 Reply Last reply Reply Quote 0
        • cxam
          cxam @SGaist last edited by

          @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.

          Stay Hungry, Stay Foolish

          1 Reply Last reply Reply Quote 0
          • SGaist
            SGaist Lifetime Qt Champion last edited by

            Good, I'll fix my memory then :)

            Did you check what lastQuery returns ?

            How are you using that query ?

            Interested in AI ? www.idiap.ch
            Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

            cxam 1 Reply Last reply Reply Quote 0
            • cxam
              cxam @SGaist last edited by

              @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

              Stay Hungry, Stay Foolish

              1 Reply Last reply Reply Quote 0
              • Paul Colby
                Paul Colby last edited by

                @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)
                
                1 Reply Last reply Reply Quote 0
                • SGaist
                  SGaist Lifetime Qt Champion last edited by

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

                  Good point of @paul-colby

                  Interested in AI ? www.idiap.ch
                  Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                  1 Reply Last reply Reply Quote 0
                  • First post
                    Last post