SQL - Adding a flag based on results within a query - best practice?

Question

I've got three tables in a mySQL DB:

tbl_user and tbl_item and tbljn_userItem (a table containing the primary keys to the first two, in a many-to-many relationship.)

I want to create a SQL statement that will return a list of all the items from tbl_item with a flag value that identifies those items that are assigned to a specific userID (tbl_user primary key).

Essentially a combination/derivative of:

SELECT tbl_item.*
FROM  tbl_item, tbljn_userItem
WHERE tbljn_useritem.userID=tbl_user.userID

and

SELECT * FROM tbl_item

SQL is not my first language so if this is an obvious Q, my apologies.

tbl_user

|- userID -|- firstName -|- lastName -|- streetAddress -|- city -|

tbl_item

|- itemID -|- name -|- description -|- category -|- cost -|

tbljn_userItem

|- userID -|- itemID -|

So, I'd give the SQL statement the userID and it would hopefully return (example):

|- itemID -|- name -|- description -|- category -|- cost -|- isOwned -|
|- itemID -|- name -|- description -|- category -|- cost -|- isOwned -|
|- itemID -|- name -|- description -|- category -|- cost -|- isOwned -|
|- itemID -|- name -|- description -|- category -|- cost -|- isOwned -|

Where isOwned is a boolean indicating that this item's itemID was listed in tbljn_userItem in the same record as the passed userID.

I realize I could easily do this in my source code, not SQL. However, I'd like to do this "the right way" and have it optimized. Am I mistaken in my assumption that if I do this on the SQL side, it will be more efficient? I'm doing multiple users at runtime and I felt it would be more efficient to do one DB call per.

All help is appreciated!

Answer 1

CREATE TABLE tbl_user
(
userId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
lastName VARCHAR(100) NOT NULL
);

-- drop table tbl_item;
CREATE TABLE tbl_item
(
itemId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
theName VARCHAR(100) NOT NULL,
description VARCHAR(255) NOT NULL,
category VARCHAR(20) NOT NULL,  -- normalize this?
cost DECIMAL(10,2) NOT NULL
);

-- drop table tbljn_userItem;
CREATE TABLE tbljn_userItem
(
userId INT NOT NULL,
itemId INT NOT NULL,
PRIMARY KEY (userId,itemId) -- in addition maybe a 2nd index going the other way, another covered index
-- will leave FK RI to you
);

INSERT tbl_user(lastName) VALUES ('smith'),('collins'),('jacobs');
INSERT tbl_item (theName,description,category,cost) VALUES ('flashlight','a working flashlight','camping',14.95);
INSERT tbl_item (theName,description,category,cost) VALUES ('mittens','mittens','winter wear',8.95);
INSERT tbl_item (theName,description,category,cost) VALUES ('scarf','red scarf, fuzzy','winter wear',8.95);
INSERT tbl_item (theName,description,category,cost) VALUES ('kite','yellow kite','kids',8.95);
INSERT tbljn_userItem (userId,itemId) VALUES (3,2),(2,2),(1,2);
-- select * from tbl_user;
-- select * from tbl_item;
-- select * from tbljn_userItem;

SELECT i.itemId,
i.theName,
i.description,
i.category,
i.cost,
( CASE 
    WHEN j.itemId IS NULL THEN 'no' ELSE 'yes' END
) AS isOwned
FROM tbl_item i
LEFT JOIN tbljn_userItem j ON j.itemId=i.itemId AND j.userId=1
LEFT JOIN tbl_user u
ON u.userId=j.userId AND u.userId=1
ORDER BY i.itemId

itemId  theName     description           category     cost   isOwned  
1       flashlight  a working flashlight  camping      14.95  no       
2       mittens     mittens               winter wear  8.95   yes      
3       scarf       red scarf, fuzzy      winter wear  8.95   no       
4       kite        yellow kite           kids         8.95   no