[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

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
0
SETP
Asked:
SETP
  • 3
  • 2
  • 2
  • +1
1 Solution
 
SETPAuthor Commented:
I can see my drawing didn't come out right because Experts-Exchange insists on using a font where each character does not take up the same amount of space, such as a Console font!!! Let me draw the two tables like this:

Table1 -> File_Num
Table2 -> File_Num, Name
0
 
davidrichardsonCommented:
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
0
 
davidrichardsonCommented:
Sorry This Will Work

SELECT Table1.File_Num, Table2.File_Num, First(Table2.Name) AS FirstOfName
FROM Table1 LEFT JOIN Table2 ON Table1.File_Num = Table2.File_Num
GROUP BY Table1.File_Num, Table2.File_Num
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
arif_eqbalCommented:
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
0
 
arif_eqbalCommented:
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
0
 
cyberdevil67Commented:
select Table1.File_Num, Table2.name
from  Table1
inner join Table1 on Table1.File_Num = Table2.File_Num
0
 
SETPAuthor Commented:
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.
0
 
arif_eqbalCommented:
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now