Select from Table where field like (Select another_field from...

[Baradaran]

Dear community,

today I am facing a new problem, maybe due to a black out. I am trying to select from a table based on the results from a select statement, however, the result only contains the first few characters of the field.

The main table has the field "category" which contains e.g. "Accessories for".
The referring table has a field "category_group" which contains e.g. "Accessories".

I have written a Select statement as following

 SELECT id FROM main_table WHERE category = (SELECT category_group FROM reffering_table WHERE category_group = 'Accessories');

This of course there are no results, as the main_table has no such content. I would like to place a like inside this select statement, so that it would work. I thoght about

SELECT id FROM main_table WHERE category LIKE 'Accessories%';

This query works perfectly. How can I place 'Accessories%' into a SELECT statment? Something like

SELECT id FROM main_table WHERE category LIKE "(SELECT category_group FROM reffering_table WHERE category_group = 'Accessories')%";

I appreciate your replies.

You can use a JOIN:

SELECT m.id FROM main_table AS m INNER JOIN reffing_table AS r ON m.category = r.category_group

To join on a related field, BUT as this seems on odd table structure you have.

main_table

id (int) | category_id (int) | otherfields...

reffing_table

id (int) | category_group (varchar) | ...

The related fields here should be main_table.category_id and reffing_table.id - so on integers.

SELECT m.id FROM main_table AS m INNER JOIN reffing_table AS r ON m.category_id = r.id WHERE r.category_group LIKE ?

USe PDO/mysqli or whatever you use to create a prepared statement and bind the 'like' value:

$stmt->execute(array($param . '%')); //PDO e.g.

[Baradaran]

Dear Diafol,

Thank you for your prompt reply. I didn't think of join. I will give it a try now and get back to you.

[Baradaran]

I still have a problem, which I will try to describe.

A costumes provider has created a table with different product groups, like
Accessoires/Schmuck / Jewelry
Accessoires/Masken / Masks
Accessoires/Handschuhe & Stulpen / Gloves & Gaiters
Accessoires/Nipple Patches & Brustwarzenklemmen...

We have created a table product groups, in which we have a column called pg_link. In that column we have entered the first common words for the category, in this case accessoires.

The product table has the following structure...

id | product_category | ... | ...

Our referring_table has the following structure:

id | product_catgory_first_common_words | ... | ...

I need to have one statement which grabs the content from product_catgory_first_common_words and place them into another statement.

The main statement is something like

SELECT * FROM products WHERE catrgory LIKE 'Accessoires%';

The word in LIKE is pulled from the DB in a second statement like

SELECT pg_group FROM referring_table WHERE id = 1;

The final query is supposed to be something like

SELECT * FROM products WHERE category LIKE (SELECT pg_group FROM referring_table WHERE id = 1);

However, the % sign is missing. Sounds like I am doing something totally wrong. :)

[Baradaran]

I found the solution. :)

The % sign is simply inserted by the scripting language.

Edited:
I mean that I use two queries instead of one.