select / 2tables using desc

Hallo,
I'm from german, so I can't write english not verry well!

My problem is the follows:
There are two tables
both with an Column there are inside the same
the first one is a main table with entries like PersonalDB FirstName, LastName, Birthday aso...
the second one is a table "under" the main table with entries like PersonalDB EntryDate, Message, User, timestamp aso...

that means the second table has any entries wich will show if choose one entry from the first table. Understand me?

first table secon table
1. 1.1
2. 1.2
3. 1.3
2.1
2.2
3.1
3.2
...

Well, I'm looking for a posibility that you can see all entries of the first tables and the youngest data of the second table
LIKE:

first table second table
1. 1.3
2. 2.2
3. 3.2

Understand me? I hope so! And I need YOUR help!
Please help, if you can an send me an email or post your answer behind my posting!

Thanks a lot!
By,
Hannes

I have used the follow select-statement:

SELECT rma.rmanr, geraet, verlauf.bearbeiter, verlauf.massnahme, verlauf.timestamp, verlauf.status
FROM rma, verlauf
WHERE rma.rmanr=verlauf.rmanr
ORDER BY verlauf.timestamp desc

this is a sort but it is not grouped!
If I grouped this I have got fals datas!
I have to have a statement wich will first order an after this the result has to group!

Thanks a lot!

You write English better than I write German...

So, if I understand your question, you would like to do a JOIN of two tables based on a field that appears in both tables ("rmanr?").

As I just learned today, JOINs of this type aren't possible with MySQL 3.22. In Mysql 3.23, it is possible to create a TEMP table in which you can create all of the fields from both tables. It's a messy JOIN but it works.

If I am wrong, I would love to know how to do this more cleanly. Ideas?

Yes, joins are possible with 3.22.xx. I use joins constantly and normalization is quite useless without them.

Hannes,

You haven't said HOW you'd like the query grouped, and I don't see any functions in the query where a GROUP BY clause is going to be used.

I'm also curious as to WHY you need a join here since rmanr is the only column you are getting from table rma (unless geraet is from that table, it's lacking a table designator).

My selct-Statement is the follows:
SELECT verlauf.RMANr,rma.geraet,verlauf.Bearbeiter,verlauf.Status,verlauf.TimeStamp
from rma,verlauf
where rma.rmanr=verlauf.rmanr and verlauf.status<>'geschlossen'
GROUP BY verlauf.RMANr
order by verlauf.rmanr, verlauf.timestamp";

The result isn't the right result!
Better use
first ORDER BY and then GROUP BY, but this is a false sql-statement - understand me!

The table "verlauf" will grow verry fast! So I think it is not a good idea using a TEMP TABLE!

looking forward to your answers!
Nice to received your messages!!!
Thanks a lot!

Hannes

I still don't understand why you are using a GROUP BY clause. GROUP BY is used when you are using a function that would operate across several records (such as count(), avg(), min(), max(), etc) and you want to group the results by the value of another column. You are not using one so the GROUP BY should have no affect. The best I can understand from your explanation, this:

order by verlauf.rmanr, verlauf.timestamp

should be enough to do what you want.

order by verlauf.rmanr, verlauf.timestamp


Actually that is enough for him to sort it in a proper way.

Hannes,,
see the mysql manual. their you can see some examples for group by and order by.
It has explained when you should use group by and order by clauses.




------------------
SR -
shiju.dreamcenter.net

"The fear of the LORD is the beginning of knowledge..."

[This message has been edited by Shiju Rajan (edited June 23, 2000).]