Click here to Skip to main content
15,900,973 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Table 1
ID    FNAME		MNAME   MOB    SEG
1		A		S		11		11
2		A		D		12		11
3		C		H		13		12

TABLE 2
ID	DATE	NAT	SEG
1	01		KK	11
2	02		KK	11

I need to fetch records with matching SEG but GROUP BY SEG
OUTPUT:
NAME	MOB	SEG	DATE	NAT
AS		11	11	01		KK


What I have tried:

SQL
SELECT * FROM TABLE1,TABLE2 WHERE TABLE1.SEG=TABLE2.SEG GROUP BY SEG
Posted
Updated 4-Apr-18 1:26am
v3

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.
SQL
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
SQL
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 [ ]
 
Share this answer
 
v2
Comments
Maciej Los 4-Apr-18 7:27am    
5ed!
Wendelius 4-Apr-18 15:14pm    
Well explained! 5 from me.
Try this:
SQL
SELECT t1.FNAME + t1.MNAME, t1.MOB, t1.SEG, t2.DAT, t2.NAT
FROM TABLE1 AS t1
    INNER JOIN TABLE2 AS t2 ON t1.SEG = t2.SEG
ORDER BY t1.ID


Output:
NAME	MOB	SEG	DATE	NAT
AS		11	11	01		KK
AS		11	11	02		KK
AD		12	11	01		KK
AD		12	11	02		KK


For further details, please see: Visual Representation of SQL Joins[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900