SETP
asked on
SQL Query
I have two tables which are joined by a field called "File_Num":
Table1 Table2
====== ======
File_Num File_Num
Name
These 2 tables are linked by the string field "Fine_Num". This field is unique in Table1, but can repeat in Table2. If I want a query that will return all File_Num's in Table1, together with the first (or otherwise any) occurance of Name in Table2, is this possible?
So if in Table1, File_Num = 'R00001', and in Table2 there are two matching records for this File_Num, say 'John Doe' and 'Justin Smith', it should return only the first one (or if not possible any one):
File_Num Name
======== ========
R00001 John Doe
Table1 Table2
====== ======
File_Num File_Num
Name
These 2 tables are linked by the string field "Fine_Num". This field is unique in Table1, but can repeat in Table2. If I want a query that will return all File_Num's in Table1, together with the first (or otherwise any) occurance of Name in Table2, is this possible?
So if in Table1, File_Num = 'R00001', and in Table2 there are two matching records for this File_Num, say 'John Doe' and 'Justin Smith', it should return only the first one (or if not possible any one):
File_Num Name
======== ========
R00001 John Doe
try something like
SELECT table1.File_Num, First(table2.File_Num) AS FirstOfFile_Num,
FROM table2 RIGHT JOIN table1 ON table2.File_Num = table1.File_Num
GROUP BY table1.File_Num, table2.File_Num
SELECT table1.File_Num, First(table2.File_Num) AS FirstOfFile_Num,
FROM table2 RIGHT JOIN table1 ON table2.File_Num = table1.File_Num
GROUP BY table1.File_Num, table2.File_Num
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi SETP
Which database are you running your Query against ??
because SQL Server does not support something like First(Column Name)
as suggested by davidrichardson
I think if we need to use Functions it would be database specific Query only
Which database are you running your Query against ??
because SQL Server does not support something like First(Column Name)
as suggested by davidrichardson
I think if we need to use Functions it would be database specific Query only
Well I think you need to use the Max function on the second table's column
This is the modified Query of the one give by davidrichardson
SELECT Table1.File_Num AS File_Num, MAX(Table2.Name) AS Name
FROM Table1 INNER JOIN Table2 ON Table1.File_Num = Table2.File_Num
GROUP BY Table1.File_Num, Table2.File_Num
This is the modified Query of the one give by davidrichardson
SELECT Table1.File_Num AS File_Num, MAX(Table2.Name) AS Name
FROM Table1 INNER JOIN Table2 ON Table1.File_Num = Table2.File_Num
GROUP BY Table1.File_Num, Table2.File_Num
select Table1.File_Num, Table2.name
from Table1
inner join Table1 on Table1.File_Num = Table2.File_Num
from Table1
inner join Table1 on Table1.File_Num = Table2.File_Num
ASKER
Hey guys. Sorry - was out for the day so I'm only going to get a chance to check the solutions now. To answer arif_eqbal, I am using an Access database. Sorry - I should have specified from the start. Thanks for all your contributions. Will get back to this thread as soon as I've tested out the queries.
Max wuold work anywhere I suppose
just try out this
SELECT Table1.File_Num AS File_Num, MAX(Table2.Name) AS Name
FROM Table1 INNER JOIN Table2 ON Table1.File_Num = Table2.File_Num
GROUP BY Table1.File_Num, Table2.File_Num
just try out this
SELECT Table1.File_Num AS File_Num, MAX(Table2.Name) AS Name
FROM Table1 INNER JOIN Table2 ON Table1.File_Num = Table2.File_Num
GROUP BY Table1.File_Num, Table2.File_Num
ASKER
Table1 -> File_Num
Table2 -> File_Num, Name