Having multiple outputs for INNER JOIN
i have two tables: PGImage and PGGallery
PGGallery has the following columns: GalleryID, Image1ID, Image2ID, Image3ID, Image4ID, Image5ID
PGImage has the following columsn: ImageID, Headline, Description, URL
I want to have a single stored procedure to grab the first GalleryID from PGGallery and select all the elements of EACH of the 5 ImageID's
How can I do this?
PGGallery has the following columns: GalleryID, Image1ID, Image2ID, Image3ID, Image4ID, Image5ID
PGImage has the following columsn: ImageID, Headline, Description, URL
I want to have a single stored procedure to grab the first GalleryID from PGGallery and select all the elements of EACH of the 5 ImageID's
How can I do this?
Last Comment
ASKER
yes.... the stored procedure will start off with Select TOP 1....
and end with ORDER BY PGGallery.DateAdded DESC....
and end with ORDER BY PGGallery.DateAdded DESC....
If it is the smallest GalleryID (perhaps it is an IDENTITY column) try something like this:
Select Top 1 *
From PGGallery g
Inner Join PGImage i1 On g.Image1ID = i1.ImageID
Inner Join PGImage i2 On g.Image1ID = i2.ImageID
Inner Join PGImage i3 On g.Image1ID = i3.ImageID
Inner Join PGImage i4 On g.Image1ID = i4.ImageID
Inner Join PGImage i5 On g.Image1ID = i5.ImageID
Order By g.GalleryID
Select Top 1 *
From PGGallery g
Inner Join PGImage i1 On g.Image1ID = i1.ImageID
Inner Join PGImage i2 On g.Image1ID = i2.ImageID
Inner Join PGImage i3 On g.Image1ID = i3.ImageID
Inner Join PGImage i4 On g.Image1ID = i4.ImageID
Inner Join PGImage i5 On g.Image1ID = i5.ImageID
Order By g.GalleryID
Than try it like this:
Select Top 1 *
From PGGallery g
Inner Join PGImage i1 On g.Image1ID = i1.ImageID
Inner Join PGImage i2 On g.Image1ID = i2.ImageID
Inner Join PGImage i3 On g.Image1ID = i3.ImageID
Inner Join PGImage i4 On g.Image1ID = i4.ImageID
Inner Join PGImage i5 On g.Image1ID = i5.ImageID
Order By g.DateAdded -- If you use DESC you will get the last, not the first
Select Top 1 *
From PGGallery g
Inner Join PGImage i1 On g.Image1ID = i1.ImageID
Inner Join PGImage i2 On g.Image1ID = i2.ImageID
Inner Join PGImage i3 On g.Image1ID = i3.ImageID
Inner Join PGImage i4 On g.Image1ID = i4.ImageID
Inner Join PGImage i5 On g.Image1ID = i5.ImageID
Order By g.DateAdded -- If you use DESC you will get the last, not the first
Oops that is not correct, it should be:
Select Top 1 *
From PGGallery g
Inner Join PGImage i1 On g.Image1ID = i1.ImageID
Inner Join PGImage i2 On g.Image2ID = i2.ImageID
Inner Join PGImage i3 On g.Image3ID = i3.ImageID
Inner Join PGImage i4 On g.Image4ID = i4.ImageID
Inner Join PGImage i5 On g.Image5ID = i5.ImageID
Order By g.DateAdded
Select Top 1 *
From PGGallery g
Inner Join PGImage i1 On g.Image1ID = i1.ImageID
Inner Join PGImage i2 On g.Image2ID = i2.ImageID
Inner Join PGImage i3 On g.Image3ID = i3.ImageID
Inner Join PGImage i4 On g.Image4ID = i4.ImageID
Inner Join PGImage i5 On g.Image5ID = i5.ImageID
Order By g.DateAdded
ASKER
i think you are on the right track however what the above is producing is the PGGallery Info. I need the PGImage info for each of the PGGallery Images...
The TOP 1 is only for the PGGallery....so after it selects the TOP 1 PGGallery, it then shows 5 rows of PGImage info (1 row for each PGGallery.Image*ID)....doe
The TOP 1 is only for the PGGallery....so after it selects the TOP 1 PGGallery, it then shows 5 rows of PGImage info (1 row for each PGGallery.Image*ID)....doe
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
ok, forget putting it on 1 row. all i want is the ImageID's grabbed from the latest PGGallery row. is this possible? i am assuming that each PGImage will have all its information on its own row.
I am not following you. Try posting some sample data and the output desired.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Please define "first". Do you have a date column?