DynDeveloper.com

DynDeveloper.com

Sign Up Now! Log In
DavidM 4/14/2015 9:31:23 AM

SQL - Return the first row in a child table with multiple rows

* This article, and all our great SQL (General) documentation, Is available on the SQL (General) menu

Puzzler: given a customer table with 3 lines and an address table with 6 lines, write a query to return one line per customer and only the first address.

 

--task:
--given a customer table with 3 lines and an address table with 6 lines,
--write a query to return one line per customer and only the first address.
declare @Customer table (custnmbr varchar(15), custname varchar(50) , rowid int identity)
declare @Address table (custnmbr varchar(15), address1 varchar(20), city varchar(20), rowid int identity)
 
insert into @Customer (custnmbr, custname) values ('Able', 'Able Technology')
insert into @Customer (custnmbr, custname) values ('Baker', 'Baker Delivery')
insert into @Customer (custnmbr, custname) values ('Charlie', 'Charlie Bakery')
 
insert into @Address (custnmbr, address1, city) values ('Able', '123 Main', 'Reno')
insert into @Address (custnmbr, address1, city) values ('Able', '123 Oak', 'Las Vegas')
insert into @Address (custnmbr, address1, city) values ('Baker', '123 Persimmon', 'Buffalo')
insert into @Address (custnmbr, address1, city) values ('Baker', '123 Apple', 'Rochester')
insert into @Address (custnmbr, address1, city) values ('Charlie', '123 Elm', 'Tampa')
insert into @Address (custnmbr, address1, city) values ('Charlie', '123 Walnut', 'Miami')

 

 

Version: Unknown or N/A
Section: SQL Scripts