Query for specific common fields in different rows

toodi4

I have a table with values like this:

Name Type Cat Desc
name1 1 3 blah blah
name1 1 4 blah blah
name2 1 3 blah blah
name2 1 3 blah blah
name2 1 3 blah blah
name2 2 1 blah blah

What I'm querying is all names that have type 1 and cat 3 in one row and type 1 and Cat 4 in another row. So in the above table, only name1 would be returned, since name2 doesn't have any Cat=4.

I know this can be done with a subquery like:

$sql = SELECT Name AS Name1 where Type = 1 and Cat = 3 and Name IN (select Name where type = 1 and Cat = 4 and Name = Name1);

But I'm hoping there is a more efficient way since the table is several million rows and running it this ways seems to take forever.

Does anyone have any suggestions?

Piranha

By first glance it seems like the table is not properly normlized.

A second glance gets me to ask if the names are unique, in the sence that two rows with the same name always refers to the same person/produkt. And it gets me to ask if the combination of name, type and cat is unique.

lazzerous

agreed with Pirahna.

Is it too late for you to rethink your data structures?

toodi4

Yes, it is too late. I'm working on an existing structure and it isn't likely it will change.

The table is more complex than what I presented. None of the fields or rows I presented are unique. There is a separate column I didn't display which is the primary key. It is called Token. Normally the select statements for this table are to find all the tokens associated with a specific Name, or to find the Name (and other info) for one token. These queries run fine.

What I'm trying to do is run some queries that the table wasn't really designed for. I'm trying to find a specific name, but I'm trying to do it by getting a list of names based on the fact that I know the name has a couple of tokens with certain characteristics.

It might just be that there is no better way to do this than what I am trying, but I don't consider myself an expert on mysql queries, so I thought I'd ask.

Piranha

Now you have first-hand experience about what happens when you don't normalize your database. 🙂

Since nothing is unique it becomes a little bit harder. I don't know if the following will be better or worse, try it to find out. Note that it is untested.

select name from (
select * from table where type = 1 and (cat = 3 or cat = 4) group by name, cat
) group by name having count(*) = 2

Basically the subquery is to retreive the rows that could be used and the main query is to see if there are two rows, one with cat = 3 and one with cat = 4. In the subquery it may possibly be quicker to use "and cat in (3, 4)".

By the way, correct indexing would really speed the queries up. In fact, indexing is often forgotten when it is the solution.

There have to be other ways to do the query, but this is the most obvious way that I can think of. And normalize in the future to make it easier 😉

Piranha

You are welcome, it was a pleasure to help you.