3

I am strugling forming a query to accomplish what I need using LEFT JOIN. I'm afraid that I'm going about this the wrong way, so I reach out to the community.

I have two databases, one for categories and one for items as follows

CATEGORIES
id       name          private
1        Apples        1
2        Oranges       1
3        Grapes        0


ITEMS
id       name          category
1        Mcintosh        1
2        Fuji            1
3        Green           3

The Question

What I need to do is form a query that will select only the CATEGORIES that are private = 1 and that also have cases assigned to their category id (ie. cannot have 0 cases such as the Oranges category).

So in this case, my query would result in only 1 result: Apple

Phiter
  • 14,570
  • 14
  • 50
  • 84
Mark
  • 3,653
  • 10
  • 30
  • 62

5 Answers5

0

You cannot perform what you ask with a LEFT JOIN. To get rid of the categories that don't have any occurrences in them, you need a INNER JOIN:

SELECT DISTINCT(c.name)
FROM categories c
INNER JOIN items i
ON c.id = i.category
WHERE c.private = 1;

Here is a working example: http://sqlfiddle.com/#!9/9f15f/5

Here you have a very nice explanation of JOINS in SQL and why what you need is a INNER JOIN: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

barbarity
  • 2,420
  • 1
  • 21
  • 29
0

You won't need to use join, the in statement will work just fine. As you can see it verifies if the desired value is in a selected list.

SELECT * FROM CATEGORIES WHERE private = 1 and id in (select distinct category from items)

See it in action

Phiter
  • 14,570
  • 14
  • 50
  • 84
  • "In" may be very slow. I wouldn't use it in a such a simple query. http://stackoverflow.com/questions/5018284/mysql-in-queries-terribly-slow-with-subquery-but-fast-with-explicit-values – Axalix Jan 31 '16 at 00:48
0
select c.* from
categories c,
(select distinct category from items) i 
where c.id = i.category and c.private = 1
Axalix
  • 2,831
  • 1
  • 20
  • 37
0

What you need is INNER JOIN and DISTINCT

 SELECT DISTINCT name FROM CATEGORIES 
 INNER JOIN ITEMS ON CATEGORIES.id=ITEMS.category 
 WHERE CATEGORIES.private=1
WebArch
  • 79
  • 1
  • 10
0

It will be usfull if you tell us index and table sizes to optimize performance, I think this is the rigth approach

SELECT c.Name FROM Categories c 
   INNER JOIN (SELECT DISTINCT category FROM items) i 
     ON c.id = i.category 
WHERE c.private = 1
Horaciux
  • 6,322
  • 2
  • 22
  • 41