Select all from table1 and some from 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

SQL Server 2005 Forums

Transact-SQL (2005)

Select all from table1 and some from table 2

Author

Topic

hydrogen666
Starting Member

3 Posts

Posted - 2007-08-14 : 18:55:28

Hello there,

I have two tables that relate to eachother. I need to get all records from table1 and say the top 3 from table2 that relate to it. The simplest form of my tables is this...

TABLE1
ID 'primary key
CategoryTitle

TABLE2
ID 'primary key
Table1_ID 'foreign key to .ID of table1
GalleryTitle

So what I need is:

CategoryTitle
....GalleryTitle
....GalleryTitle
....GalleryTitle
CategoryTitle
....GalleryTitle
....GalleryTitle
....GalleryTitle
CategoryTitle
....GalleryTitle
....GalleryTitle
....GalleryTitle

can anyone help?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-14 : 19:23:30

just a simple INNER JOIN will do the job

SELECT c.CategoryTitle, g.GalleryTitle
FROM   TABLE1 c INNER JOIN TABLE2 g
ON     c.ID = g.TABLE1_ID

And for the formatting or presentation of data, do it in your front end application.

KH
[spoiler]Time is always against us[/spoiler]

hydrogen666
Starting Member

3 Posts

Posted - 2007-08-14 : 19:49:50

in which case, I may be being dumb, but how do I display

Category 1
...Gallery1
...Gallery2
...Gallery3
Category2
...Gallery4
...Gallery5
...Gallery6

etc...?

I'm not sure how to get a 'category' and the relative three 'galleries' (if obviously, at least three galleries have a linking ID).

In english, how do I write the 'category' just once with a few of the galleries underneath, then the same again for the next category?

I'm actually working in ASP, but I have a basic knowledge of PHP if you lot would rather explain like that :-)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-14 : 20:00:26

where do you want to display the data ? What front end application / tools are you using ?

KH
[spoiler]Time is always against us[/spoiler]

hydrogen666
Starting Member

3 Posts

Posted - 2007-08-14 : 20:12:44

It's an ASP page as part of a much bigger system on a website of mine. I'm just using ASP and VbScript to run the site.

I just can't think how to do it. I'll sleep on it I think.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources