Join to give list with optional relationship column

Question

Here are the tables:

         sets: id INT

        users: id INT

        items: id INT
               setid INT [an item only belongs to one set]

relationships: userid INT
               itemid INT
               relationship WHATEVER

Now, I have been trying to write an SQL query to do the following, without much success.

Given a particular setid and userid, we need to generate a list of all the items in the set, with a column containing the given user’s relationship to each item, if such a relationship exists. i.e. ideally, columns in the results would be:

setid
itemid
relationship

My best effort is with two inner join statements as follows:

select
    sets.id as setid,
    items.id as itemid,
    relatonships.relationship as relationship

from sets
    inner join items on sets.id = items.setid 
    inner join relationships on relationships.itemid = items.id

where
    sets.id = [say, 5]
    and relationships.userid = [say, 27]

However, obviously this doesn’t work, because the second where clause eliminates rows where there is no existing relationship between that item and the given user. I can see that you could perform two queries, but that would seem... wrong for such a seemingly basic problem.

I’m sure this is a simple problem and I apologise for being such an SQL newb!

Edit

Example: Two items, 5 and 6, are in set 1. The current userid is 15. 15 has an existing relationship defined with item 5, but not with item 6.

In this case, running the above query in mysql will return one row only: setid: 1, itemid: 5, relationship: X.

The reason for this is that the second where clause eliminates the row containing item 6, because for that set of results from the various tables, relationships.userid = 15 returns false.

This happens regardless of whether an inner join or left join is used.

Answer 1

Not 100% what you are asking, by try this

select
    sets.id as setid,
    items.id as itemid,
    relatonships.relationship as relationship

from sets
    inner join items on sets.id = items.event 
    LEFT join relationships on relationships.itemid = items.id

where
    sets.id = [say, 5]
    and relationships.userid = [say, 27]

Keep in mind that the relationship column will be NULL if none exists. Also, the relationship reference in the WHERE clause could eliminate rows as well. Hope this gets you start, again I might misunderstand what you are trying to achieve. From your table design, there does not seem to be a relationship between the user and the sets, could you possibly the list of fields in the tables.

Try the following query:

select sets.id as setId,
       items.id as ItemId,
       relationships.relationship
from sets
    join users on 1=1
    join items on sets.id=items.setid
    left join relationships on relationships.itemId = items.id
where 
    sets.id=5 and users.id = 27

By adding the users table to the join with 1=1, you do not need the where clause to reference the relationship table at all

Change the left join to:

left join relationships on relationships.itemId = items.id 
AND relationships.userId=users.id

Answer 2

Found the solution: no second where clause, and no (clever!) fudging with join users on 1=1 is required.

select * from sets
    inner join items on items.setid = sets.id
    left join relationships
        on relationships.itemid = items.id
        and relationships.userid = [say, 5]
where
    sets.id = [say, 1]

In other words, the solution was to move the selection of the user in the relationships table from a where clause to an extra predicate in second join.