Where clause for only recs that match all in 2nd table.

[rotorblade]

I'm trying to retrieve only records from table A joined to table B where A.id = B.id and there is a matching record in table B for EVERY value in a list of values for B.filter.

For instance, given the following two tables:

-------- Table A ----------
| id | description |
---------------------------
| 2 | "blu + yel" |
| 3 | "just blue" |
| 4 | "just blue" |
---------------------------

-------- Table B ----------
| id | filter |
---------------------------
| 2 | yellow |
| 2 | blue |
| 3 | blue |
| 4 | blue |
---------------------------
I need a where clause to join A and B such that I get only records from A where there is a matching record in B with filter='yellow' _AND_ a matching record in B with filter='blue' (i.e only id=2)

The _OR_ version is simple (B.value IN ('yellow', 'blue')) but that returns ANY record from A where ANY value matches. I need only the records from A where __ALL__ values are present in B.

Please help!!

Thanks!

-R

 

[tlbroadbent]

The table design you've described makes the creation of a query very difficult if not impossible. The desription uses abbreviations rather than full words. The Table A desription contains multiple values in a row which you want to match to multiple rows in Table B. You will need to code each combination or use a cursor to accomplish what you have described.

Expanding on the OR query, you could code an AND query.

Select * From tableA
Where Exists (Select * From tableB Where Value = 'blue')
And Exists (Select * From tableB Where Value = 'yellow')

It is easy to hard code this selection criteria. In my opnion the problem is matching the values contained in the tableA description to the tableB values. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[r937]

i agree, the table design makes for difficuilt if not impossible queries

the essential problem is knowing how many terms are in each value of tableA.description -- 'red,white,blue' has three terms, 'blue,yellow' has two terms, and so on

if you could count the number of commas in description, then that plus 1 would be the number of terms, but i know of no function that counts the number of occurrences of a substring in a string

if you can store the number of terms in the description column in an additional column in tableA, call it tableA.terms, then you can do

select tableA.id, description, terms
  from tableA
inner join tableB
    on tableA.id = tableB.ID
group by tableA.id, description, terms
having terms 
     = sum( CASE 
            WHEN LOCATE(filter,description) > 0
            THEN 1 ELSE 0 )

here LOCATE() is a mysql function which does substring searching... you might also try LIKE

rudy

http://rudy.ca/

 

[rotorblade]

Okay I shouldn't have complicated things by even including the description column in table A. Its value is only to demonstrate the desired results. Suppose that instead of the description column, table A had a completely unrelated column called blob_data. My objective is to select all blob_data values for which there exists a B.filter='blue' AND B.filter='yellow'. Table A and table B are joined purely on the 'id' column, and nothing else.

I'm hoping there is some way to express this in SQL without sub-selects. If there is, I would expect it to look something like the following:

SELECT A.blob_data FROM A, B
WHERE A.id = B.id
AND B.filter ?MATCHES_ALL_OF? ('yellow', 'blue');

Maybe it isn't possible. If not, I'd like to know what table design would help to accomplish this goal.

Thanks,

-R

 

[rotorblade]

Well, I got an answer from another source that is similar to but simpler than that suggested by r937. In case anyone is interested, the trick was to use GROUP BY and HAVING COUNT(A.id) = x, where x is calculated in the application program code at the same time the list of filter values is generated. An example query ends up looking like this:

SELECT A.blob_data FROM A, B
WHERE A.id = B.id
AND B.filter IN ('yellow', 'blue')
GROUP BY A.id
HAVING COUNT(A.id) = 2;

Thanks all!

-R

 

[fluteplr]

You realize that will only work correctly if you can guarantee that there are not more than one entry for a single id for a single color.

 

[rotorblade]

I did consider that. The PK for table B is (id, filter) so uniqueness shouldn't be a problem.

 

[r937]

> the trick was to use GROUP BY and
> HAVING COUNT(A.id) = x, where x is calculated
> in the application program code

that's cheating ;o)

it's basically the same as my suggestion

having terms 
     = sum( CASE 
            WHEN LOCATE(filter,description) > 0
            THEN 1 ELSE 0 )

which works for any number of terms, not just two hardcoded ones

the SUM() will equal x whenever all the terms are found

also, your query --

SELECT A.blob_data FROM A, B
  WHERE A.id = B.id
    AND B.filter IN ('yellow', 'blue')
  GROUP BY A.id
 HAVING COUNT(A.id) = 2;

is not valid, because you cannot GROUP BY a column that's not in the SELECT list...

... at least not in ANSI SQL ;o)

your comment "I'm hoping there is some way to express this in SQL without sub-selects" reveals what you're working with -- but i sympathize with you, because i couldn't find a mysql forum here either

note to moderators: the hierarchical categorization of available forums leaves plenty to be desired

i am not a programmer, and almost didn't find the ANSI SQL forum...

 

[r937]

> the trick was to use GROUP BY and
> HAVING COUNT(A.id) = x, where x is calculated
> in the application program code

that's cheating ;o)

it's basically the same as my suggestion --

having terms 
     = sum( CASE 
            WHEN LOCATE(filter,description) > 0
            THEN 1 ELSE 0 END)

(note: forgot the END the first time i posted) which works for any number of terms, not just two hardcoded ones

SUM() will equal terms whenever all the filters are found

also, your query --

SELECT A.blob_data FROM A, B
  WHERE A.id = B.id
    AND B.filter IN ('yellow', 'blue')
  GROUP BY A.id
 HAVING COUNT(A.id) = 2;

is not valid, because you cannot GROUP BY a column that's not in the SELECT list...

... at least not in ANSI SQL ;o)

your comment "I'm hoping there is some way to express this in SQL without sub-selects" reveals what you're working with -- but i sympathize with you, because i couldn't find a mysql forum here either

note to moderators: the hierarchical categorization of available forums leaves plenty to be desired

i am not a programmer, and almost didn't find the ANSI SQL forum...