CODEWITHSUNDEEP
mysql
Lawrence H.
Lawrence H.

Reputation: 92

Joining Results From Another Table

I'm dealing with a large query that maps data from one table into a CSV file, so it essentially looks like a basic select query--

SELECT * FROM item_table

--except that * is actually a hundred lines of CASE, IF, IFNULL, and other logic.

I've been told to add a "similar items" line to the select statement, which should be a string of comma-separated item numbers. The similar items are found in a category_table, which can join to item_table on two data points, column_a and column_b, with category_table.category_id having the data that identifies the similar items.

Additionally, I've been told NOT to use a subquery.

So I need to join category_table and group_concat item numbers from that table having the same category_id value (but not having the item number of whatever the current record would be).

If I can only do it with a subquery regardless of the instructions, I will accept that, but I want to do it with a join and group_concat as instructed if possible--I just can't figure it out. How can I do this?

Upvotes: 0

Views: 44

Answers (3)

pmorken
pmorken

Reputation: 784

Maybe something like this?

SELECT i.*, GROUP_CONCAT(c.category_id) AS similar_items
  FROM item_table i
INNER JOIN category_table c ON (i.column_a = c.column_a AND
                                i.column_b = c.column_b)
GROUP BY i.column_a, i.column_b

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271141

You can make use of a mySQL "feature" called hidden columns.

I am going to assume you have an item id in the item table that uniquely identifies each row. And, if I have your logic correct, the following query does what you want:

select i.*, group_concat(c.category_id)
from item_table i left outer join
     category_table c
     on i.column_a = c.column_a and
        i.column_b = c.column_b and
        i.item_id <> c.category_id
group by i.item_id

Upvotes: 1

sgeddes
sgeddes

Reputation: 62861

I think this is what you're looking for, although I wasn't sure what uniquely identified your item_table so I used column_a and column_b (those may be incorrect):

SELECT 
    ...,
    GROUP_CONCAT(c.category_id separator ',') CategoryIDs
FROM item_table i
   JOIN category_table ct ON i.column_a = ct.column_a AND
      i.column_b = ct.column_b
GROUP BY i.column_a, i.column_b

I've used a regular INNER JOIN, but if the category_table might not have any related records, you may need to use a LEFT JOIN instead to get your desired results.

Upvotes: 1

Related Questions