CODEWITHSUNDEEP
mysql
Devin
Devin

Reputation: 2246

Check for match in other column

I am trying to fabricate an SQL query that will provide these results:

  | Category Title | Subcategory Of |
  -----------------------------------
  |   Category 1   |                |
  |   Category 2   |                |
  |   Category 3   |                |
  |   Category 4   |                |
  |   Category 5   |                |
  |   Category 6   |   Category 4   |
  |   Category 7   |   Category 5   |

This is what my database looks like:

CREATE TABLE `categories` (
  `category_id` int(4) NOT NULL AUTO_INCREMENT,
  `subcategory_id` int(4) NOT NULL,
  `category_title` longtext COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `categories` (`category_id`, `subcategory_id`, `category_title`) VALUES
(1, 0, 'Category 1'),
(2, 0, 'Category 2'),
(3, 0, 'Category 3'),
(4, 0, 'Category 4'),
(5, 0, 'Category 5'),
(6, 4, 'Category 6'),
(7, 5, 'Category 7');

I thought that you would use JOIN, but I wasn't able to mentally think of what kind of query to run, since as far as I knew JOIN was for joining two tables, not two columns. I'm new to these advanced queries (I'm good with INSERT, UPDATE, DELETE, etc. though). Any help is appreciated.

This is what I was trying, which makes no sense really.

SELECT * FROM categories RIGHT JOIN categories ON subcategory_id = category_id

Upvotes: 1

Views: 68

Answers (2)

BHS
BHS

Reputation: 1081

as far as I knew JOIN was for joining two tables, not two columns

A better way to think about JOIN is that it defines the relationship in your query between columns.

There is no restriction that the columns being joined be in different tables. The only issue is how to refer to them, which you do using aliases, as described by a previous answer. Even when joining different tables the query is, usually, easier to read if you use aliases for the table names.

Aliases are also useful when you need to join two (or more) tables with identical column names.

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838156

It's called a self-join. You incldue the table name twice in the query, but giving it two different aliases and then it's just like a normal join:

SELECT
    C1.category_title AS category_title,
    C2.category_title AS subcategory_of
FROM categories C1
LEFT JOIN categories C2
ON C1.subcategory_id = C2.category_id

Upvotes: 1

Related Questions