How to query distinct and non-distinct data?

[orclord1]

I am a very new web coder, and I am trying to figure out a way to look for unique values on a joined table.

table a (“minis”) contains a column for value “cats_id”. This is a numeric value that links to table b. Table a contains numerous rows of data which all contain one of 4 numbers in the “cats_id” column.

table b (“categories”) contains the unique column “cats_id” and has a column for “cats_name”.

I want to run a query that finds all the distinct “cats_id” numbers in table a and joins the result so that “cats_name” from table b can be referenced in my output.

I can’t figure out how to do this

SELECT DISTINCT cats_id FROM minis LEFT JOIN categories ON minis.cats_id = categories.cats_id WHERE cats_id = ?;

The problem with the above query, is that the result does not include “cats_name” from the categories table.

How can I do this?

[r937]

SELECT DISTINCT 
       categories.cats_id
     , categories.cats_name
  FROM minis 
INNER
  JOIN categories 
    ON categories.cats_id = minis.cats_id

[m_hutley]

The lesson Rudy is trying to impart being that DISTINCT works on the entire tuple of selected row values, so even if you have two categories with the same name, the ID would be unique such that you will select all unique category ID’s in your result set.

[igor_g]

SELECT categories.*
FROM categories 
WHERE categories.cats_id 
   IN  (SELECT DISTINCT cats_id FROM minis)

[r937]

yes, that and something else –

i changed LEFT JOIN to INNER JOIN because from the original problem statement i’m fairly sure any invalid minis.cats_id values (values that don’t exist in categories, i.e. minis in categories that don’t exist) should be excluded

[orclord1]

Thank you for everyone’s help in assisting me to learn this.