return 10 records table 1 and 1 record table 2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

return 10 records table 1 and 1 record table 2

Author

Topic

fresh2006
Starting Member

9 Posts

Posted - 2006-01-05 : 12:40:11

I have looked everywhere but cannot find the solutions. I have two tables.

Table1 = tblvacation (10 records)
fields vacation_id, land, destination
Table2 = tblimages (1000 records)
fields image_id, vacation_id, folder, name

Both are related through vacation_id. Every vacation however has many pictures. I need a query where the result would give me back all records (vacations) from Table 1 with only 1 record from Table 2(one image per vacation).

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-05 : 13:06:56

Here's an example of one way to return all columns for both tables (only image for minimum image_id)


use northwind
set nocount on
go
create table tblVacation 
	(vacation_id int primary key
	,land int
	,destination int)
go
create table tblImage 
	(image_Id int primary key
	,vacation_id int references tblVacation(vacation_id)
	,folder int
	,name int)
go

insert tblVacation 
select 1,1,1 union
select 2,2,2 union
select 3,3,3

insert tblImage
select 1,1,1,1 union
select 2,1,1,1 union
select 3,1,1,1 union
select 4,2,2,2 union
select 5,2,2,2 union
select 6,3,3,3 union
select 7,3,3,3
go

select	a.vacation_id
	,a.land
	,a.destination
	,c.image_Id
	,c.folder
	,c.[name]
from	tblvacation a

join	(--derived table returns minimum image_id for each vacation_id
	select	vacation_id
		,min(image_id) image_id
	from	tblimage
	group by vacation_id
	) b

		on b.vacation_id = a.vacation_id
join	tblimage c
		on c.image_id = b.image_id


go
drop table tblimage
go
drop table tblvacation

Be One with the Optimizer
TG

fresh2006
Starting Member

9 Posts

Posted - 2006-01-05 : 13:22:52

I think this works... Thanx a million my friend. This has taken me the whole day...

fresh2006
Starting Member

9 Posts

Posted - 2006-01-05 : 13:57:46

Hmmm it works fine but I have to put in a where clause... both tables have a field vriend_id

SELECT a.vakantie_id, a.vriend_id, a.land, a.bestemming, c.image_id, c.mapnaam, c.naam, c.vriend_id AS Expr1
FROM tblvakanties AS a INNER JOIN
(SELECT vakantie_id, MIN(image_id) AS image_id
FROM tblimages
WHERE (vriend_id = 94)
GROUP BY vakantie_id) AS b ON b.vakantie_id = a.vakantie_id INNER JOIN tblimages AS c ON c.image_id = b.image_id

fresh2006
Starting Member

9 Posts

Posted - 2006-01-05 : 14:02:55

got it...

SELECT a.vakantie_id, a.vriend_id, a.land, a.bestemming, c.image_id, c.mapnaam, c.naam, c.vriend_id AS Expr1
FROM tblvakanties AS a INNER JOIN
(SELECT vakantie_id, MIN(image_id) AS image_id
FROM tblimages
GROUP BY vakantie_id) AS b ON b.vakantie_id = a.vakantie_id INNER JOIN
tblimages AS c ON c.image_id = b.image_id AND a.vriend_id = 94

fresh2006
Starting Member

9 Posts

Posted - 2006-01-05 : 14:18:04

hmmm that is strange. last post works in query analyzer but won't in my asp.net vb page... any syntac for I have to think of ?

Error occured while Generating Data. Error is System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'c'. Incorrect syntax near the keyword 'AS'.

Anybody a clue ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources