MYSQL SYNTAX JOIN

Question

I have two tables. One is items. The second is items by categories (catitems). Both tables have a user field.

I need a query that selects all items by user in the first table (items) but excludes those items in the second table (catitem) assigned to a given category. In other words, I get a list of items by user from the first table but if there is a row in the 2nd table that has item, cat and user, I exclude the item.

Schematically this would be something like Select item FROM items where userid=1 but exclude item WHERE (in second table) (cat = "something" AND userid=1.)

I've tried the following but it's not excluding the item present in table 2. What am I doing wrong?

$sql = "SELECT i.*
FROM `items` i
LEFT JOIN `catitems` c
ON i.userid = c.userid
WHERE (c.userid = '$userid' && c.tcircle != '$cat')";

How do you match the items in the second table to those in the first? Can you show your table schema (the output of SHOW CREATE TABLE) for both tables? — eggyal
– eggyal, Commented Apr 30, 2012 at 15:36

Matt Ball · Accepted Answer · 2012-04-30 14:18:56Z

1

First:

$sql = "SELECT i.*
FROM `items` i
LEFT JOIN `catitems` c
ON i.userid = c.userid
WHERE c.userid = '$userid'
AND c.tcircle != '$cat'";

Second: sql-injection. Solution: pdo.

answered Apr 30, 2012 at 14:18

Matt Ball

361k102 gold badges655 silver badges725 bronze badges

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

1 Comment

user1260310 Over a year ago

This also does not exclude the items for which a row is present in 2nd table.

DRapp · Accepted Answer · 2012-04-30 14:26:17Z

0

A LEFT JOIN means all from left-side table (table listed first) vs a required match on the right-side. HOWEVER, once you apply the right-side table in a WHERE clause, it technically FORCES it to an inner join. As such, to fix your need, you need to move the "condition" associated with the other table to its corresponding ON clause...

SELECT i.*
   FROM items i
           LEFT JOIN catitems c
              ON i.userid = c.userid
             AND c.tcircle != '$cat'
   WHERE 
      i.userid = '$userid'

Small, but subtle difference. You ALWAYS want the "ITEM" associated for a user (via where i.userid instead of c.userid), but CONDITIONLLY, you only want the categories that are NOT '$cat' (found at the ON clause of the join)

answered Apr 30, 2012 at 14:26

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

2 Comments

user1260310 Over a year ago

To be clear, I do not want every item in left table for user...I want to exclude from those items, items found in right table. Above query still not excluding match in right table.

user1260310 Over a year ago

Joins did not seem well suited to task but following employing subquery works: $sql = "SELECT * FROM items WHERE userid = '$userid' AND id NOT IN (SELECT itemid FROM catitems WHERE userid='$userid' AND tcircle = '$cat')";

Collectives™ on Stack Overflow

MYSQL SYNTAX JOIN

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related