CODEWITHSUNDEEP
sql
user2904018
user2904018

Reputation:

Issue with joins, SQL statement

I am trying to write an SQL statement but I am having trouble. I have the following tables

Notes

person_user VARCHAR
message VARCHAR
time DATETIME
id INT (primary Key)

Person

name (primary key)
address VARCHAR
telephone INT

Associated_With

person_associating VARCHAR
person_associating_with VARCHAR

I need for the notes to appear, only the notes that have written by the person_associating_with in the Associated_With table.

This is my attempt as of now:

SELECT * 
FROM Notes 
INNER JOIN Notes.person_user ON Notes.id AS associatedMessages
INNER JOIN Associated_With On associatedMessages
WHERE 
     Person.name = person_associating
ORDER BY 
     time DESC;

At the moment I am getting all possible results back, regardless of the query

Upvotes: 0

Views: 86

Answers (2)

Lloyd Banks
Lloyd Banks

Reputation: 36638

I am assuming you are looking for the following:

SELECT *
FROM Notes AS ABB2 
   JOIN Associated_With AS ABB1 ON 
       ABB1.person_associating_with = ABB2.person_user
ORDER BY time DESC

In your original query, you are trying to join tables with both ON conditions and WHERE conditions. While it's possible to do so, it's best to use one or the other for consistency.

Upvotes: 1

Filipe Silva
Filipe Silva

Reputation: 21657

If i understand correctly, you might not be looking for joins but an IN statement:

SELECT *
FROM Notes
WHERE person_user IN
    (SELECT person_associating_with
     FROM Associated_With);

Upvotes: 0

Related Questions