CODEWITHSUNDEEP
sqlmysql
Alexander
Alexander

Reputation: 41

Query to get name from a table with 2 ID columns

I have 2 tables. The first has a structure like

id (int)
parent_id (int)
category_name (varchar)

The second has a structure like:

id (int)
old_category (int)
new_category (int)

I want to make a query to pull the old category and new category names all in one query for the parents. That is, I want to get the name of the old parent, then get the name of the new parent. The second table contains a historical list of all the parent ID changes made in the first table. How can I do this? Thanks!

Upvotes: 0

Views: 1907

Answers (4)

CrazyDart
CrazyDart

Reputation: 3801

Join them?

select * from table1
  join table2 j1 on table2.id = table1.old_category
  join table2 j2 on table2.id = table1.new_category

Upvotes: 0

johnny
johnny

Reputation: 19735

I will take a guess at what you meant:

tblOne

id

cat_name

tblTwo

id

tblOneId (old_cat) int

new_cat int

select * from tblOne t1, tblTwo t2 Where t1.id = t2.tblOneId

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116110

SELECT
  t.id,
  t.old_category,
  oc.category_name as old_category_name,
  t.new_catevory,
  nc.category_name as new_category_name
FROM
  table1 t
  INNER JOIN table2 oc on oc.id = t.old_category
  INNER JOIN table2 nc on nc.id = t.new_category

I would seriously consider some modifications in column names, btw. To start: why don't the categories in table2 have an id postfix?

Upvotes: 0

Stephen
Stephen

Reputation: 18964

   SELECT old_cat.category_name, new_cat.category_name
     FROM join_table
LEFT JOIN category_table as old_cat
       ON old_cat.id = join_table.old_category
LEFT JOIN category_table as new_cat
       ON new_cat.id = join_table.new_category;

If you've managed to get this far, it's time to stop coding and start learning MySQL JOIN Syntax.

Upvotes: 1

Related Questions