Well the first problem you are going to get is
Ambiguous column name 'SEG'.
Because SEG exists in both TABLE1 and TABLE2 you have to explicitly tell Oracle which one you want to use e.g.
SELECT * FROM TABLE1,TABLE2 WHERE TABLE1.SEG=TABLE2.SEG GROUP BY TABLE1.SEG
But that code will just lead you to your next problem ...
Column 'TABLE1.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
You are probably never going to come across a situation where
SELECT * is sensible when used with
GROUP BY. In fact, it is far better practice to always list the columns you want explicitly - it can be more efficient but it also stops legacy code from breaking if there are table schema changes in the future.
You're also using a very old fashioned way of joining tables - use explicit
JOIN types and
ON clauses instead e.g.
SELECT FNAME+MNAME, MOB, T1.SEG, [DATE], NAT
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.SEG = T2.SEG
This code will give you
AS 11 11 1 KK
AD 12 11 1 KK
AS 11 11 2 KK
AD 12 11 2 KK
The next part of the problem is your expected results. It's not clear what logic you were applying to expect only that one line of results. The only way I could see to get it was
SELECT MAX(FNAME+MNAME), MIN(MOB), T1.SEG, MIN([DATE]), MAX(NAT)
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.SEG = T2.SEG
GROUP BY T1.SEG
but I'm pretty sure that is
not what you really want.
As an aside you will see that I've used square brackets around
[DATE]. That's because DATE is a reserved word and shouldn't be used for column names unless it is enclosed in [ ]
Submit an article or tip