CODEWITHSUNDEEP
sqlsql-server
Sunny Sandeep
Sunny Sandeep

Reputation: 1011

find only one row from table in foreign with another tables

i have 2 tables gallery(Gallery_ID,Name) and Gallery_Image(ID,Gallery_ID,Image). My question is i want to select only one image of each gallery

i have tried this query

SELECT distinct  top 1  Gallery.Gallery_ID, Gallery.Gallery_Name,Gallery.Gallery_Name  as Gallery_Image
FROM         Gallery 
    union
select distinct Gallery_Image.Gallery_ID,Gallery_Image.Gallery_Images as Gallery_Name,Gallery_Image.Gallery_Images
from Gallery_Image inner join Gallery on Gallery.Gallery_ID=Gallery_Image.Gallery_ID
where Gallery_Image.Gallery_ID in(select Gallery_ID from Gallery)

Upvotes: 0

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You say: "i want to select only one image of each gallery." This makes sense. I have no idea how your query relates to this question.

You don't specify which database you are using. A good approach is to use row_number() to assign a sequential order to the images within a gallery. The key is to do a random sort. The last piece depends on the database. Here is the SQL Server syntax:

select gi.*
from (select gi.*, row_number() over (partition by Gallery_Id order by newid()) as seqnum
      from Gallery_Image gi
     ) gi
where seqnum = 1;

EDIT:

To get information from the Gallery table, join it in:

select gi.*
from (select gi.*, row_number() over (partition by Gallery_Id order by newid()) as seqnum
      from Gallery_Image gi
     ) gi join
     Gallery g
     on gi.Gallery_Id = g.Gallery_Id and
        seqnum = 1;

I also moved the logic to take the "first" image to the on clause from the where clause.

Upvotes: 1

Related Questions