Query Question

[duces_wild]

ahh yes but i will not let there be duplicate names
hehehe.
ill give it a whirl and see what happens

[cybose]

I have a table "testTable" with columns A and B with the following
values...


A B
------ ------
P1 Q1
P1 Q2
P1 Q3
P2 Q1
P2 Q3
P3 Q1
P3 Q2
P3 Q3

I need to select only those values from column A that have the values
(Q1,Q2 AND Q3) in column B.

The result I'm expecting is something like...

P1 Q1
P1 Q2
P1 Q3
P3 Q1
P3 Q2
P3 Q3


What would be the form of the query ?

[Bill Karwin]

cybose wrote:

> I need to select only those values from column A that have the values
> (Q1,Q2 AND Q3) in column B.

Here are a couple of solutions:

SELECT DISTINCT T.A, T.B
FROM T
JOIN T1 ON T.A = T1.A AND T1.B = 'Q1'
JOIN T2 ON T.A = T2.A AND T2.B = 'Q2'
JOIN T3 ON T.A = T3.A AND T3.B = 'Q3'

SELECT A, B
FROM T
WHERE A IN (
SELECT A
FROM T AS T2
WHERE T2.B IN ('Q1', 'Q2', 'Q3')
GROUP BY T2.A
HAVING COUNT(DISTINCT T2.B) = 3)

The latter solution uses a subquery, therefore it requires MySQL 4.1 or
later.

Regards,
Bill K.

[cybose]

Thanks !

However, the aim wass to get all rows that "for a given value in A has
all the specified values in B"

For instance if only Q1 and Q3 are specified, the result should be -

A B
---- ----
P2 Q1
P2 Q3

and if only Q1 is specified-

A B
---- ----
P1 Q1
P2 Q1
P3 Q1

and if Q1,Q2 and Q3 are specified-

A B
---- ----
P1 Q1
P1 Q2
P1 Q3
P3 Q1
P3 Q2
P3 Q3

That seems to be hard to model with a nested query.

Would I have to do something like

ResultSet1 = SELECT * FROM T WHERE B = "Q1"
ResultSet2 = SELECT * FROM T WHERE B = "Q2"
ResultSet3 = SELECT * FROM T WHERE B = "Q3"

and then find the intersection of these resultsets.


But it looks like there'll be as many resultsets to intersect
as there are Qs. Can this be avoided?

And in your solution using joins how are T1, T2, T3 defined?

Regards,
VB



Bill Karwin wrote:

> cybose wrote:

> > I need to select only those values from column A that have the values
> > (Q1,Q2 AND Q3) in column B.

>
> Here are a couple of solutions:
>
> SELECT DISTINCT T.A, T.B
> FROM T
> JOIN T1 ON T.A = T1.A AND T1.B = 'Q1'
> JOIN T2 ON T.A = T2.A AND T2.B = 'Q2'
> JOIN T3 ON T.A = T3.A AND T3.B = 'Q3'
>
> SELECT A, B
> FROM T
> WHERE A IN (
> SELECT A
> FROM T AS T2
> WHERE T2.B IN ('Q1', 'Q2', 'Q3')
> GROUP BY T2.A
> HAVING COUNT(DISTINCT T2.B) = 3)
>
> The latter solution uses a subquery, therefore it requires MySQL 4.1 or
> later.
>
> Regards,
> Bill K.

[Bill Karwin]

cybose wrote:

> But it looks like there'll be as many resultsets to intersect
> as there are Qs. Can this be avoided?

Yes, by using the subquery solution; list the values in the IN clause of
the subquery, and change the number in the HAVING clause of the subquery
to the quantity of values.

> And in your solution using joins how are T1, T2, T3 defined?

Sorry, I did it wrong. T1, T2, and T3 are correlation names (aka table
aliases) for T. For instance:

....JOIN T AS T1 ON T.A = T1.A AND T1.B = 'Q1'

Regards,
Bill K.

[cybose]

> Yes, by using the subquery solution; list the values in the IN clause of
> the subquery, and change the number in the HAVING clause of the subquery
> to the quantity of values.

If I wanted to select only those rows that had both "Q1 AND Q3 "...

SELECT A, B
FROM T
WHERE A IN (
SELECT A
FROM T AS T2
WHERE T2.B IN ('Q1', 'Q3')
GROUP BY T2.A
HAVING COUNT(DISTINCT T2.B) = 2)

The Result is

+------+------+
| A | B |
+------+------+
| P1 | Q1 |
| P1 | Q2 |
| P1 | Q3 |
| P2 | Q1 |
| P2 | Q3 |
| P3 | Q1 |
| P3 | Q2 |
| P3 | Q3 |
+------+------+

And not

A B
---- -----
P2 Q1
P2 Q3

I think, the WHERE clause above, is more like an OR expression when
whats needed is an AND type expression...

Regards,
Vibeesh

[Bill Karwin]

Aha -- it wasn't clear from your original question that you wanted
groups from A that map _only_ to Q1 and Q2, not those that include Q1
and Q2 but might have others.

How about this solution:

SELECT A, B
FROM T
WHERE A IN (
SELECT T2.A
FROM T AS T2
GROUP BY T2.A
HAVING GROUP_CONCAT(DISTINCT T2.B ORDER BY T2.B) = 'Q1,Q2');

See [url]http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html[/url] for
details on the GROUP_CONCAT() function.

This solution also works only in MySQL 4.1 and later, since the
GROUP_CONCAT() function is not implemented in MySQL 4.0.

Regards,
Bill K.