SQL

simple little SQL
I have two tables
'#############
table1 is called [Surname]
field1 = Id
field2 = Surname
id    surname
[Surname] data
1     moon
2     Blaire
3     White
'#############
table2 is called [FirstName]
field1 = num
field2 = id
field3 = firstname
[Firstnames] data
Num Id firstname  
1   1      John
2   2   pete
3   1   mark
4   2   louis
5   3   joe
6   1   noel
7   1   mike

I want an sql to give me the first occurence of each surname with the first [firstname] only
so for the data above this is the result i am expecting

moon , John
Blaire ,pete
white ,joe

cheers T
turloughmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimCotteeHead of Software ServicesCommented:
IF these are access tables then the following query will return the required values

SELECT DISTINCTROW Min(Firstname.num) AS MinOfnum, Min(Firstname.id) AS MinOfid, First(Firstname.Forname) AS FirstOfForname, Min(Surnames.surname) AS MinOfsurname
FROM Surnames LEFT JOIN Firstname ON Surnames.id = Firstname.id
GROUP BY Surnames.id;
0
ryanvsCommented:
I guess TimCottee's answer will work, but only if the database supports the First function which I believe is not ANSI standard.  Personally, I like the following code better.

SELECT
  Surname.Surname,
  Firstname.Firstname
FROM
  Firstname INNER JOIN
  Surname ON
    Firstname.Surname = Surname.ID
WHERE
  Firstname.num In (
    SELECT Min(Firstname.num)
    FROM Firstname
    GROUP BY Firstname.Surname
  )
0
ryanvsCommented:
oops, change the SQL statement to

SELECT
  Surname.Surname,
  Firstname.Firstname
FROM
  Firstname INNER JOIN
  Surname ON
    Firstname.Id = Surname.Id
WHERE
  Firstname.num In (
    SELECT Min(Firstname.num)
    FROM Firstname
    GROUP BY Firstname.Id
  )

I didn't look closely enough at your fieldnames before I posted.  Sorry.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

TimCotteeHead of Software ServicesCommented:
ryanvs, I don't disagree with you at all. Your method is probably more elegant but for access db my way does work.
0
dhodgeCommented:
In standard SQL

SELECT Firstname.num , surname.surname
FROM Firstname , surname
where firstname.id = surname.id
and
  Firstname.num In
(
    SELECT Min(Firstname.num)
    FROM Firstname
    GROUP BY Firstname.Id
)

Bosh, forget all this mamby pamby shove it in access rubbish!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ryanvsCommented:
Talk about trying to pick up cheap points...  That happens to be the same answer I gave or don't you know that the inner join is exactly the same join you gave?
0
dhodgeCommented:
If you REALLY new SQL you would realize why that comment is not true.
0
ryanvsCommented:
If you REALLY _knew_ SQL and could read the MS-SQL Books Online (with which you supposedly have 8 years exp.) or ANSI SQL-92 you would see INNER JOIN is not an "Access" thing.  It is simply the ANSI syntax for specifying foreign key joins.  I have used both methods and I don't mind using either one.  By definition, "where firstname.id = surname.id" is an inner join (just it doesn't use the ANSI join syntax) - you are joining two tables surname with a primary key and firstname with a foreign key and showing only the rows that match.

Did you actually try the SQL statement I gave?  I tested both queries in MS-SQL 6.5 and the query plans are identical for both.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.