SQL - Select rows from two different tables

Question

Having this table

Table "Items"

itemID
itemTitle
itemContent

and this

Table "MyList"

userID
itemID
deleted

how can I select all rows from table "Items" and showing the field "deleted", even if the itemID do not exist in "MyList", given an userID?

Example of the query result:

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

What would be the query, so that I can get that result?

Thanks

score 16 · Accepted Answer · 2009-02-10 15:49:37Z

16

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 OP's comment:

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

edited Feb 10, 2009 at 15:49

answered Feb 10, 2009 at 15:07

user21926

Sign up to request clarification or add additional context in comments.

Comments

RSilva · Accepted Answer · 2009-02-10 16:17:17Z

0

I'm not sure if this is the best way of doing 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

answered Feb 10, 2009 at 16:17

RSilva

6,95311 gold badges45 silver badges50 bronze badges

Comments

Chani Poz · Accepted Answer · 2013-10-08 09:39:27Z

0

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

edited Oct 8, 2013 at 9:39

Chani Poz

1,4632 gold badges21 silver badges47 bronze badges

answered Feb 10, 2009 at 15:11

Arve

7,5245 gold badges40 silver badges44 bronze badges

Collectives™ on Stack Overflow

SQL - Select rows from two different tables

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related