I got a SQL problem. Let's say i have three tables with data as illustrated:
|-----|-----| |-----|-----| |---- -|--------|
| ITEMS | | USERITEMS | | USERS |
|-----|-----| |-----|-----| |------|--------|
|Id |name | |user |ID | |userID|usename |
|-----|-----| |-----|-----| |------|--------|
|1 |no1 | | 1 | 1 | | 1 |john |
|2 |no2 | | 1 | 2 | | 2 |frank |
|3 |no3 | | 1 | 3 | | 3 |paul |
|4 |no4 | | 2 | 1 | | 4 |jess |
|5 |no5 | | 3 | 1 | | 5 |hans |
|6 |no6 | | 3 | 2 | | 6 |mary |
|7 |no7 | | 4 | 1 | | 7 |kelly |
|8 |no8 | | 4 | 2 | | 8 |shane |
|-----|-----| |-----|-----| |------|--------|
Every user can have items according to ITEMS. This is represented in USERITEMS. What i'm trying to do is to make a SQL statement that really should include a subquery
Like this:
SELECT Items.ID, Items.name FROM Items, Useritems, Users WHERE
Useritems Where Users.userID<>Useritems.User AND userID=1
What i'm trying to do is simply list all items that John lack.
Problem is is that I get ALL user's items twice and John's once. How do i only get the items John lacks?
I hope i explained the problem good enough. The SQL is written from memory and may not be good at all 🙂
Thanks in advance.
Olav Bringedal
