CODEWITHSUNDEEP
phpmysqlsqljoin
michaeln
michaeln

Reputation: 1052

MySQL - select all rows which user doesn't own

I'm having a small MySQL problem. I want to select all rows of my table which contain data that the user doesn't own (it's an exchange tool).

Example: 

id  |  owner  |  event
----------------------
1   |    4    |    3
2   |    5    |    3
3   |    3    |    2
4   |    5    |    6

I'm owner #4. I want to see all events except the ones I already own, so I want to get a result like: 

event |  offered by
-------------------
2     |     3
6     |     5

Is is possible to select this via SQL or do I have to select all the users own events and remove all other events with the same id from the result?

Upvotes: 0

Views: 125

Answers (3)

Ivan Yonkov
Ivan Yonkov

Reputation: 7034

SELECT event, offered_by FROM events_table WHERE owner <> 4

or if they are 2 table for example events and offers

SELECT tb1.event, tb1.offered by FROM offers as tb1
INNER JOIN events as tb2 on tb1.event = tb2.event
WHERE tb2.owner <> 4

Upvotes: 0

Alex
Alex

Reputation: 540

I'm just assuming php here

SELECT event
FROM EXAMPLE_TABLE
WHERE owner NOT IN '$owner_id'

Upvotes: -1

John Woo
John Woo

Reputation: 263703

SELECT  a.event, a.owner AS `Offered By`
FROM    tableName a
WHERE   a.event NOT IN
        (
            SELECT  b.event
            FROM    TableName b
            WHERE   owner = 4
        )

or by using JOIN which I preferred more,

SELECT  a.event, a.owner AS `Offered By`
FROM    tableName a
        LEFT JOIN tableName b
            ON  a.event = b.event AND
                b.owner = 4
WHERE   b.event IS NULL

OUTPUT

╔═══════╦════════════╗
║ EVENT ║ OFFERED BY ║
╠═══════╬════════════╣
║     2 ║          3 ║
║     6 ║          5 ║
╚═══════╩════════════╝

Upvotes: 7

Related Questions