'SQL query for displaying all columns in one table after looking at 2 columns in a different table

I'm very new to SQL queries so forgive me if this is a simple question.

I have 2 tables with the column names in parenthesis:

  • Client (clientID, username, password)
  • Statements (statementID, clientID, charges)

I want to show all records from the Statements table but I'm currently given username. I want to use the username to get the clientID from the Client table so that I could use the clientID in the Statements table.

How would I query this?

Edit: below is my best attempt at querying this

SELECT *
FROM Statements
WHERE clientID IN
    SELECT clientID
    FROM Client
    WHERE username='user1'
mysqlsql


Solution 1:[1]

The only issue with your current query is missing parenthesis:

SELECT * 
FROM Statements 
WHERE clientID IN (
  SELECT clientID 
  FROM Client 
  WHERE username='user1'
);

Another approach would be to use exists, such as

 SELECT * 
 FROM Statements s
 WHERE EXISTS (
   SELECT * FROM CLIENT c
   WHERE c.clientID = s.clientID
     AND c.username='user1'
);

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stu

Related Questions