Searching a many-to-one table structure

[cmancone]

I've got two tables set up such that each entry in table 'A' can have many options in table 'B', and I'm not sure how to search this when there are multiple search criteria in table 'B'.

Each table has a unique id, and one of the fields in table 'B' is the id of the entry in table 'A' that the option applies to. For a single search criteria in table 'B' I would do something like this:

SELECT * FROM A,B WHERE A.id=B.A_id AND B.value='value1'

However I don't know how to do this if I only care about the entries from A that have multiple matching entries in B (i.e. A must have two entries in B with values of 'value1' and 'value2'). I know there's going to be an easy way to do this, but unfortunately I'm not as familiar with mysql as I should be...

[Old Pedant]

It depends on whether the multiple matches must occur in the *SAME RECORD* of table B or must occur in *different* records of table B or may occur in either the same or different records.

Must occur in same record:

SELECT {list of fields}
FROM A, B
WHERE A.id=B.A_id
AND B.x='value1' AND B.y='value2'


Must occur in *different* records:

SELECT {list of fields}
FROM A, B as B1, B as B2
WHERE A.id = B1.A_id
AND A.id = B2.A_id
AND B1.id <> B2.id
AND B1.x = 'value1'
AND B2.y = 'value2'

(x and y *could* be the same field in this situation).

May occur in same *or* different records:

SELECT {list of fields}
FROM A, B as B1, B as B2
WHERE A.id = B1.A_id
AND A.id = B2.A_id
AND B1.x = 'value1'
AND B2.y = 'value2'

x and y should be different fields in this situation, of course.

********

By the by, this isn't really a MySQL question. The answer would be the same for any ANSI relational database system.

Note that you should not use SELECT * when pulling data from two or more tables. (And almost never should use it in any circumstances.)

[Old Pedant]

Should note that the third option there could well give you multiple results for the same A record.

[cmancone]

Thanks! Number 2 is what I was looking for. So why is it that I should avoid using SELECT * in general?

[abduraooft]

So why is it that I should avoid using SELECT * in general?It'd be very inefficient as it may fetch a bunch of columns which are not required for your search.

[cmancone]

Okay, that's what I figured.

[Old Pedant]

Also, if you have the same column name in more than one of the JOINed tables, it can lead to confusion (if not programming errors). In the rare instance where I need to get the same-named column from two tables, I use an alias on at least one of the columns:

SELECT A.name, B.name AS 'bName'
FROM A, B
WHERE ...

[cmancone]

Thanks for all the help so far - one more question. It occurs to me that I might be doing this in the wrong way in general. The trouble is that there can be an arbitrary number of options (although it's likely to be less than 10). Therefore, I'm searching for between 1 and 10 different options. The trouble it seems is the bit where I exclude selecting the same option more than once:

WHERE B1.id <> B2.id

For four options this becomes:

WHERE B1.id <> B2.id AND B1.id <> B3.id AND B1.id <> B4.id AND B2.id <> B3.id

etc...

Basically the number of conditions in my search criteria scales roughly as the number of options squared, which is clearly not good :( Is there a more succinct way to accomplish this search, or do I just need to reorganize my program/database schema?

[cmancone]

Duh! Never mind on that last post (although feel free to answer it if you want). I realized that I did do this the smart way, and I have additional information such that I don't have to worry about excluding the same entry from being selected more than once.

[Old Pedant]

Well, there *might* be an entirely different way of doing this, if you can indeed guarantee that you don't need to exclude dupes.

Is the situation *ACTUALLY* that the field tested in B1 and B2 is actually the same field name??? Such as:

SELECT {list of fields}
FROM A, B as B1, B as B2
WHERE A.id = B1.A_id
AND A.id = B2.A_id
AND B1.xxx = 'value1'
AND B2.xxx = 'value2'

???

If so, why not just do this:

SELECT A.field1, A.field2, A.field3, COUNT(DISTINCT B.xxx)
FROM A, B
WHERE A.id = B.A_id
AND B.xxx IN ( 'value1','value2','value3')
GROUP BY A.field1, A.field2, A.field3
HAVING COUNT(DISINCT B.xxx) = 3