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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Please define "first". Do you have a date column?