Only one row for each fldVaraID

Question

Only one row for each fldVaraID

fiaolle

SSC Enthusiast

Points: 163
More actions
March 30, 2010 at 1:35 am

#220724

Hi
I have this SQL query
SELECT dbo.tblVara.fldKategori, dbo.tbVara.fldVaraID, dbo.tblVara.fldRubrik, dbo.tblAnAddVara.fldText, dbo.tblVara.fldPris,
dbo.tblVara.fldDate, dbo.tblImage.fldImage, dbo.tblImage.fldImageID, dbo.tblLan.fldLan, dbo.tblStad.fldStad
FROM dbo.tblImage INNER JOIN
dbo.tblVara INNER JOIN
dbo.tblLan ON dbo.tblVara.fldLan = dbo.tblLan.fldLanId LEFT OUTER JOIN
dbo.tblStad ON dbo.tblVara.fldStad = dbo.tblStad.fldStadID ON dbo.tblImage.fldVaraID = dbo.tblVara.fldVaraID
I just want to have one row per fldVaraID from tblAnAddVara even if tblAnAddImage contains more than one fldVaraID.
Is it possible, hope so.:unsure:
Thanks
Fia

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 1

My First thought would be to try using ROW_Number() function and just select 1 row based on your condition if any.... can you post your table definitions with some sample data ??

fiaolle SSC Enthusiast Points: 163 More actions · Answer 2

Hi

Thanks for your reply.

The tblVara has:

fldVaraID int identity

fldKategori int

fldRubrik nvarchar

fldText nvarchar

fldPris int

The tblImage has:

fldVaraID int which is a foreign key to tblVaraID.fldVaraID

fldImage image

fldImageID int identity

It is between these tables I have the trouble. I just want to have the first or one image for the fldVaraID in tblImage.

Hope this helps and would like to see the how the query would look like if I can use the RowNumber() function

Thanks

Fia

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 3

If your SQL is getting you all the images then you just need to make rank() or row_number() function part of your SQL as below

SELECT dbo.tblVara.fldKategori, dbo.tbVara.fldVaraID, dbo.tblVara.fldRubrik, dbo.tblAnAddVara.fldText, dbo.tblVara.fldPris,

dbo.tblVara.fldDate, dbo.tblImage.fldImage, dbo.tblImage.fldImageID, dbo.tblLan.fldLan, dbo.tblStad.fldStad,RANK() OVER (PARTITION BY fldVaraID ORDER BY fldVaraID ) as MyRank

FROM dbo.tblImage INNER JOIN

dbo.tblVara INNER JOIN

dbo.tblLan ON dbo.tblVara.fldLan = dbo.tblLan.fldLanId LEFT OUTER JOIN

dbo.tblStad ON dbo.tblVara.fldStad = dbo.tblStad.fldStadID ON dbo.tblImage.fldVaraID = dbo.tblVara.fldVaraID

What that does is it ranks your rows based on fldVaraID and then you can select all rows where MyRank = 1 using CTE's or make this as a Sub query

fiaolle SSC Enthusiast Points: 163 More actions · Answer 4

Hi again

It works, thanks. But what if I also want to get the rows that doesn't have any images, meaning the rows from tblVara that doesn't have fldVaraID in table tblImage. I tried with

where t.MyRank=1 or t.MyRank=0

and

where t.MyRank<=1

but that didn't work.

Hope you can help

Fia

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 5

fiaolle (3/31/2010)
Hi again
It works, thanks. But what if I also want to get the rows that doesn't have any images, meaning the rows from tblVara that doesn't have fldVaraID in table tblImage. I tried with
where t.MyRank=1 or t.MyRank=0
and
where t.MyRank<=1
but that didn't work.
Hope you can help
Fia

SQL in your 1st post uses an INNER join between two tables which means that you will get rows only when there's a match between two tables.. You need to look at the way you are joining tables..it nothing to do with rank function.. Let me ask you this, were getting those rows before using rank function...... I believe its a NO... If you weren't getting them then you need to change the way you are joining tables probably do a left join instead of of an INNER join...

Correct me if am wrong..

fiaolle SSC Enthusiast Points: 163 More actions · Answer 6

Hi

Thanks, now it's working. I'm using a right outer join with ROW_NUMBER.

Regards

Fia