title: postgresql : how to join multiple relational tables and select row based on data in other tables
body: <p>This is the description of my problem on this <a href="https://stackoverflow.com/questions/73081134/sqlalchemy-search-in-a-complicated-relational-postgresql-database">page</a></p>
  <p>trying to get data like this didnt work for me :</p>
  <pre><code>tod_session_req = await session.execute(
                  select(
                      Users.firstname
                  ).join(
                      Users, Users.id == PrivateTods.fu_id
                  ).outerjoin(
                      Blocks, Users.id == Blocks.blocker
                  ).where(
                      Blocks.blocked != user.id,  # other user didnt block current
                  )
              )
              tod_session = tod_session_req.fetchone()
  </code></pre>
  <p><strong>I could use raw queries or other method or any help. thanks alot</strong></p>
  <p>In my bot,</p>
  <p>If a user wants to play, the bot must check the <code>Tods</code> table to see if there is a row where <code>Tods.su_id</code> value is <code>None</code>.</p>
  <p>We consider the <code>Tods.su_id</code> that wants to be connected to the <code>Tods.fu_id</code> to be <code>100</code></p>
  <p>If there was a colum <code>Tods.su_id</code> with <code>None</code> value, the bot should check the two conditions in the <code>Blocks</code> table</p>
  <p>One is whether the <code>Blocks.blocked</code> column is equal to <code>100</code> in the rows where the <code>Blocks.blocker</code> column is equal to <code>Tods.fu_id</code> or not. (Did the user <code>Users.fu_id</code> block the user with ID <code>100</code> or not).</p>
  <p>if user <code>100</code> wasnt blocked, go to the next condition.</p>
  <p>If it was: read the last two lines</p>
  <p>The next condition is :
  In the <code>Blocks</code> Table rows, where the <code>Blocks.blocker</code> column is equal to <code>100</code>, is the blocked column equal to <code>Users.fu_id</code> or not? (Did the user <code>100</code> block user <code>Users.fu_id</code> or not).</p>
  <p>If not, select that row and give it to me</p>
  <p>If it was blocked, continue browsing so that there is no user who can play with it, and in that case it will be added to the tod table because this section is related to Python, not the database.</p>