MySQL Joins

[blitzen]

I have 2 tables that are almost identical structure.
One field, "case_no" is present in both tables.

TABLE A
id ... case_no ... date ... content
1 ... 1451 ... 2011-01-05 ... trees are green
2 ... 1452 ... 2011-01-06 ... sky IS blue
3 ... 1453 ... 2011-01-08 ... this IS fun
4 ... 1454 ... 2011-01-15 ... so are you

TABLE B
id ... case_no ... date ... content
1 ... 1452 ... 2011-02-02 ... this IS happy stuff
2 ... 1454 ... 2011-02-03 ... yes, i agree
3 ... 1452 ... 2011-02-04 ... IS this the end?

From both tables as one result, I want to SELECT case_no WHERE content LIKE '%is%'
ORDER BY date DESC

And, I want UNIQUE case_no, that is, it should be in the results only one time.
The above select should return
from Table A, id=3, case 1453
from Table B, id=3, case 1452

I tired UNION and GROUP BY (GROUP BY id ORDER BY date DESC) but I get case_no 1452 showing up twice, one row from table A and the id=1 from table B.

And, why didn't the ORDER BY return from table B the id=3? I suspect it's in the GROUP BY and ORDER BY not using tablea.id but I don't know how to structure the statement to GROUP BY both tables.

Thanks in advance!

[oheso]

Have you tried GROUP BY case_no?

What you're after may be possible in MySQL alone, but is likely to be complicated (and really begs the question why there are two tables). One alternative (assuming you're fetching this from a webpage employing e.g., PHP) would be to go with the query you've got and then use the webpage's scripting language to only display the first instance of each case_no.

[blitzen]

Hi Oheso,
I ended up just using UNION and ORDER BY with each select. Then I created an array of case_no's to compare against and omit the row with same case_no.

UNION... GROUP BY isn't valid.

Why 2 tables is a long explanation, but I do need the two tables.

Thank you for your thoughts.