SQL: Joining Same Column Twice For Criteria

137
April 25, 2018, at 10:01 AM

I have a column called member.id which is present in a table called poster as both poster.sender and poster.reciever.

I need a query which selects member.value where the member.id is either the poster.sender OR poster.reciever.

[i know the names dont make much sense - i didnt come up with them]

So far i have this and it isnt working:

SELECT member.value
FROM ((poster INNER JOIN member ON poster.sender = member.ID) 
INNER JOIN poster ON poster.sender= member.id
WHERE (member.id <> ?
AND (((poster.Sender = ?) OR (poster.reciever = ?))AND (poster.Status = 'Accepted'))

Any ideas would be super helpful!

Answer 1

Try this:

SELECT 
    member.value
FROM 
    poster s INNER JOIN member ON s.sender = member.ID
INNER JOIN poster r ON r.receiver = member.id
WHERE 
    member.id <> ?
AND 
    (
        s.sender = ? 
    OR r.reciever = ?
    ) 
AND 
    s.status = 'Accepted'

you cannot use a table twice in a query without alias, hence the alias s and r.

Also change where condition according to your requirement -> status = 'Accepted' from sender or receiver table or both.

Answer 2

You'll want something like this...

select distinct(value)
from
(
(select member.value from member inner join poster on member.id = poster.sender) 
union 
(select member.value from member inner join poster on member.id = poster.receiver) 
) 
Answer 3
SELECT value
       FROM member
       WHERE EXISTS (SELECT *
                            FROM poster
                            WHERE member.id IN (poster.sender,
                                                poster.receiver));

Should be equivalent to N Mason's but probably faster. At least it doesn't need to eliminate duplicates for the UNION (hoping the optimizer ignores the outer DISTINCT as the results from the UNION are already distinct and does not uselessly do it again). Elimination of duplicates can be pricy.

READ ALSO
How to order two tables with no relationship and different columns, by a single column that they share (date)

How to order two tables with no relationship and different columns, by a single column that they share (date)

I've got two tables in a MYSQL databaseOne Is a for blog posts and one is for videos

145
MySQL ERROR 1118 Row size too large, changed VARCHARS to TEXT but still getting the error

MySQL ERROR 1118 Row size too large, changed VARCHARS to TEXT but still getting the error

I have a very "wide" MySQL table, with nearly 700 columns(I inherited this system and will be moving to a key-value store in the future, but that's outside the point of this question

105
AES_DECRYPT for login JFrame code not working

AES_DECRYPT for login JFrame code not working

I am having problems understand something related to AES_DECRYPTI made a test database in SQL and table tblKorisnici in it

176
javax.persistence.PersistenceException: No Persistence provider for EntityManager named em1

javax.persistence.PersistenceException: No Persistence provider for EntityManager named em1

I am creating a server in Glassfish and I want to have a JPA persistence layer to a remote MySQL database

185