Select operation - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Select operation

Author

Topic

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2010-06-03 : 06:52:07

Dear Friends

I am trying to select all rows from one table
and join it with the top 1 record from another table

(Basically I am selecting a list of albums and the top 1 image from the gallery database)

I have made the below to do this, however I would like to remove from the list, any albums that do not have a top 1 gallery

"where album_image is not null" - this gives out the message invalid column name. How can I reference this field to not select it if it is null

SELECT [dbo].[TB_Album].*,

(

SELECT TOP 1 gallery_image

FROM dbo.TB_gallery

WHERE (gallery_album = tb_album.album_id)

ORDER BY gallery_id ASC

) As album_image

FROM [dbo].[TB_Album]
where album_image is not null

ORDER BY [Album_order]

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-03 : 07:05:13

Why this can't be your solution ?

SELECT a.* FROM tb_Album a
INNER JOIN TB_gallery g ON a.album_id = g.gallary_album

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2010-06-03 : 07:09:51

because an album can contain many gallery_images, the above selection would return the same album for how many times there is a gallery_image that corresponds to the album id.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-03 : 07:44:11

Try this -


SELECT	a.*, g.Gallery_image
FROM	tb_Album a
		CROSS APPLY
		( 
			SELECT		top 1 Gallery_image
			FROM		TB_Gallery 
			WHERE		gallery_album = a.album_id
			ORDER BY	Gallery_id
		) g

and let me knw this solution is working or not ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2010-06-03 : 08:03:04

cross apply isn't recognised.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-03 : 08:18:59

I am not getting
are you using sql server 2005 ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2010-06-03 : 08:30:06

I managed to sort it

thank you for trying to help me =)

select * from
(
SELECT TB_Album.*,
(
SELECT TOP 1 gallery_image
FROM TB_gallery
WHERE (gallery_album = tb_album.album_id)
ORDER BY gallery_id ASC
) As album_image
FROM TB_Album
) g
where g.album_image is not null
order by g.album_order

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-03 : 09:42:25

Are you using sql server 2000?
why you cant use cross apply ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-03 : 10:20:47

Could be on a lower compatibility level.

godspeedba.

Try this instead of your subquery -- this should be quicker


SELECT
	tba.*
	, tbg.[Gallery_image]
FROM
	TB_Album AS tba

	JOIN (
		SELECT
			[gallery_album] As [gallery_album]
			, MAX([gallery_Id]) As [gallery_Id]
		FROM
			TB_gallery
		GROUP BY
			[gallery_album]
		)
		AS hGalImg ON hGalImg.[gallery_album] = tba.[album_id]

	JOIN TB_gallery AS tbg ON tbg.[gallery_Id] = hGalImg.[gallery_id]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sachin.Nand

2937 Posts

Posted - 2010-06-03 : 10:31:04

How about this one?


SELECT 
	[dbo].[TB_Album].* 
	FROM [dbo].[TB_Album] where tb_album.album_id=

(
	SELECT TOP 1 gallery_image FROM TB_gallery
	WHERE (gallery_album = tb_album.album_id)
	ORDER BY gallery_id ASC
)

PBUH

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-03 : 10:35:45

Hi Idera,

I think that the various SELECT TOP 1 queries (except for the CROSS APPLY ideas) are row operations. For each row in the parent result that SELECT TOP 1 is run. That will be slower (should be slower) than fetching the highest ID's using MAX and GROUP BY in a derived table.

OFC -- only the OP can test for sure. I think the two queries should give different execution plans.

Depends on the size of the dataset but I think the GROUP BY / MAX should be much faster.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sachin.Nand

2937 Posts

Posted - 2010-06-03 : 10:56:50

Hi TC,

Well I relooked at my query.With my one you cannot get gallery_image in the select list.

too bad if that is the requirement and it seems it is that way.

About performance as you said let the OP decide.

PBUH

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-04 : 02:29:42

quote:

Originally posted by Idera

How about this one?


SELECT 
	[dbo].[TB_Album].* 
	FROM [dbo].[TB_Album] where tb_album.album_id=

(
	SELECT TOP 1 gallery_image FROM TB_gallery
	WHERE (gallery_album = tb_album.album_id)
	ORDER BY gallery_id ASC
)

PBUH

Even this query will give an error
as you are comparing albumid with gallery_image datatype mismatch.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Sachin.Nand

2937 Posts

Posted - 2010-06-04 : 03:47:44

quote:
Originally posted by vaibhavktiwari83

quote:
Originally posted by Idera

How about this one?

SELECT
[dbo].[TB_Album].*
FROM [dbo].[TB_Album] where tb_album.album_id=

(
SELECT TOP 1 gallery_image FROM TB_gallery
WHERE (gallery_album = tb_album.album_id)
ORDER BY gallery_id ASC
)

PBUH

Even this query will give an error
as you are comparing albumid with gallery_image datatype mismatch.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

How about this one?Any bugs here to?

SELECT
*
FROM [dbo].[TB_Album] where exists

(
SELECT TOP 1 gallery_image FROM TB_gallery
WHERE (gallery_album = tb_album.album_id)
ORDER BY gallery_id ASC
)

PBUH

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-04 : 04:33:45

quote:

How about this one?Any bugs here to?

SELECT
*
FROM [dbo].[TB_Album] where exists

(
SELECT TOP 1 gallery_image FROM TB_gallery
WHERE (gallery_album = tb_album.album_id)
ORDER BY gallery_id ASC
)

PBUH

Now this is error free
but as you already told that its nt upto the op's requirement.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources