MySQL query, don't display record based on name

Question

I have a table which holds events. Each event has a name, but events are grouped based on their name.

+---------------+
| NAME          |
+---------------+
| EVENT 1 CHILD |
| EVENT 1 ADULT |
+---------------+

Is a sample row. However, when I query for the events, I only want to show one of the event names.

So essentially I only want EVENT 1 ADULT to be displayed, as the child is linked to it via it's name.

I'm not sure how I would do this purly in MySQL, I know I could perform a query then strip out the events with PHP, but it seems silly to do this if I can do it with MySQL.

Any advice on how to tackle this problem?

Thanks!

And "event 1 child" and "event 1 adult" is the contents of the 'name' col? Then I suggest you change it to 3 seperate cols with 'name', 'number', 'type' or something — Hugo Delsing, Dec 10 '12 at 11:27
@GeertJaminon the events i want to find will ALWAYS end in " ADULT" (without the quotes). — dotty, Dec 10 '12 at 11:29
@DainisAbols is there a case sensitive version of this? %ADULT% matches both lower and higher at the moment. I want upper case only. — dotty, Dec 10 '12 at 11:33
@dotty Try [this](http://stackoverflow.com/questions/8083455/mysql-like-case-sensitive) then. — Peon, Dec 10 '12 at 11:34
Thanks @DainisAbols, would you mind making this an answer so I can mark it as correct. — dotty, Dec 10 '12 at 11:35

Peon · Accepted Answer · 2012-12-10T11:53:49.020

4

You can just read the date where there is only ADULT like this:

SELECT * FROM table WHERE NAME LIKE '%ADULT%';

Or, if it is always at the end:

SELECT * FROM table WHERE NAME LIKE '% ADULT';

But, if you need it case sensitive, try this:

SELECT * FROM table WHERE NAME LIKE BINARY '%ADULT%';

edited Dec 10 '12 at 11:53

answered Dec 10 '12 at 11:36

Peon

7,902
7
59
100

+1, given her data restrictions this is the most sensible solution. – davidethell Dec 10 '12 at 11:38
@dotty, lol! my mistake. I just read the name not the profile! – davidethell Dec 10 '12 at 12:14

davidethell · Answer 2 · 2012-12-10T11:37:11.547

0

You should change your table structure to include a parent_id column. This will allow you to explicitly link child records to a parent without basing it on the name and then you can easily query for just parent records using:

SELECT name FROM events WHERE parent_id IS NULL;

EDIT:

If you can't change the data (per your comment) then you're stuck with something like this for finding parents:

SELECT name FROM events WHERE name LIKE '%ADULT%';

And for finding the child of a given adult:

SELECT name FROM events WHERE name LIKE CONCAT('%', TRIM(TRAILING 'ADULT' FROM '$parentName', '%');

Note that is a totally untested use of CONCAT with a LIKE so it's just a conjecture that it will work.

edited Dec 10 '12 at 11:37

answered Dec 10 '12 at 11:29

davidethell

11,708
6
43
63

Unfortunately the data is being fed via XML from a shop terminal. – dotty Dec 10 '12 at 11:30
Yes that is unfortunate. I have edited the answer to reflect more possibilities. – davidethell Dec 10 '12 at 11:37

MySQL query, don't display record based on name

2 Answers2