mysql INNER_JOIN subquery

Question

I have inherited a MySQL database, that has a table as follows:

mysql> describe stock_groups;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| group  | varchar(5)   | YES  |     | NULL    |                |
| name   | varchar(255) | YES  |     | NULL    |                |
| parent | varchar(5)   | YES  |     | NULL    |                |
| order  | int(11)      | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

When I run mysql> select * from stock_groups wheregroup='D2';

I get:

mysql> select * from stock_groups where `group`='D2';
+----+-------+------+--------+-------+
| id | group | name | parent | order |
+----+-------+------+--------+-------+
| 79 | D2    | MENS | D      |    51 |
+----+-------+------+--------+-------+
1 row in set (0.00 sec)

and also i have a table:

mysql> describe stock_groups_styles_map;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| group | varchar(5)  | NO   |     | NULL    |                |
| style | varchar(25) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

when I run:

mysql> select `group` from stock_groups_styles_map where style='N26';
+-------+
| group |
+-------+
| B1    |
| B11   |
| D2    |
| V2    |
+-------+
4 rows in set (0.00 sec)

how do i get the stock_groups.name ?

This is called a join and is a fundamental part of the SQL language. If you are planning on using SQL, you should learn the basics. — Gordon Linoff
– Gordon Linoff, Commented Jun 21, 2014 at 17:58
Join both tables on group and fetch the name from first table. — Rahul
– Rahul, Commented Jun 21, 2014 at 18:00
You have columns called group and order. You should sneak around to the house of the person who created this and exact a terrible revenge. Break a gnome or something. — Strawberry
– Strawberry, Commented Jun 21, 2014 at 18:53

Barranka · Accepted Answer · 2014-06-21 18:00:52Z

1

Join the tables, and select only the data you need. If you need unique rows, use the distinct keyword:

select  -- If you need unique names, use "select distinct" instead of "select"
    sg.name
from
    stock_groups_styles_map as sgs
    inner join stock_groups as sg on sgs.group = sg.group
where 
    sgs.style = 'N26'

You could also solve this using subqueries, but that would be rather inneficient in this case.

Something important

You should add the appropriate indexes to your tables. It will improve the performance of your database.

answered Jun 21, 2014 at 18:00

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

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

2 Comments

khinester Over a year ago

i get ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

Rahul Over a year ago

@khinester, you are getting the collation error most probably cause both columns in both tables have different collation scheme. to solve this just change the on clause of join like on sgs.group = sg.group collate utf8_general_ci

Barranka · Accepted Answer · 2014-06-21 18:01:19Z

0

You can use inner join on group column

SELECT ss.group, sg.name from 
stock_groups sg inner join stock_groups_styles_map ss on ss.group = sg.group 
where ss.style='N26'

edited Jun 21, 2014 at 18:01

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

answered Jun 21, 2014 at 18:00

Pranav Maniar

1,56511 silver badges22 bronze badges

Comments

khinester · Accepted Answer · 2014-06-21 18:27:20Z

0

SELECT stock_groups.name FROM stock_groups_styles_map, stock_groups WHERE stock_groups_styles_map.style ='N26';

worked for me.

answered Jun 21, 2014 at 18:27

khinester

3,5229 gold badges52 silver badges92 bronze badges

Collectives™ on Stack Overflow

mysql INNER_JOIN subquery

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related