SQL - row selection from two different tables

Question

Availability of this table

Table "Items" itemID itemTitle itemContent 

and this one

 Table "MyList" userID itemID deleted 

how can I select all rows from the "Elements" table and show the "deleted" field, even if the ItemID does not exist in "MyList", given the user ID?

Example query result:

 itemID | itemTitle | deleted | userID ------------------------------------- 1 | Title1 | 1 | 2 2 | Title2 | 0 | 2 3 | Title3 | (null) | (null) 4 | Title4 | (null) | (null) 

What will be the query to get this result?

thanks

Answer 1

I'm not sure if this is the best way to do this, but it returns what I was looking for:

 select itemID, itemTitle, deleted, userID FROM( SELECT i.id_itemID, i.itemTitle, M.deleted, M.userID FROM MyList M right OUTER JOIN Items I ON I.itemID= M.itemID ) as MyTableItems where itemID = 3 or itemID is null 

Answer 2

 SELECT I.itemID, I.itemTitle, M.deleted FROM Items I LEFT OUTER JOIN MyList M ON M.itemID = I.itemID WHERE M.userID = 9 

Edit: Based on a comment on the OP:

 SELECT I.itemID, I.itemTitle, M.deleted, M.userID FROM MyList M LEFT OUTER JOIN Items I ON I.itemID = M.itemID WHERE M.userID = 9 

Answer 3

You can use outer join :

 select a.itemId, a.itemTitle, b.deleted from Items a left outer join MyList b on a.itemId = b.itemId