turloughm
asked on
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
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
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
)
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
)
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.
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.
ryanvs, I don't disagree with you at all. Your method is probably more elegant but for access db my way does work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
If you REALLY new SQL you would realize why that comment is not true.
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.
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.
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;