UNION ALL, Joins and no Joins - Page 2

blindman · #16 (**permalink**) 03-03-08, 09:16

Sigh...
Some horses you can't even lead to water.

GWilliy · #17 (**permalink**) 03-03-08, 09:45

Neigh......

Wot's wrong Mr blindman - Sure my TSQL is childlike and could do with tidying up (easier with DDL) but methinks it does the job he's asking for.

Surely it's just a fairly basic correlated returning a TOP 1 using NEWID() to give a Random.

Performance does'nt seem to be a concern.

Or am I missing something

GW

GWilliy · #18 (**permalink**) 03-03-08, 10:08

Thought I'd better tidy up a bit

Quote:

Select title,description,simage from tableA
UNION ALL
Select title,description, simage from tableB
UNION ALL
Select title,description,(SELECT sImage FROM(SELECT TOP 1 NewID() x,sImage FROM tableW wi WHERE wi.id = TableC.id ORDER BY x)dt)
FROM TableC

Nate1 · #19 (**permalink**) 03-03-08, 12:32

Looks good, I'll give it a try tonight.

TallCowboy0614 · #20 (**permalink**) 03-03-08, 14:41

I think the horse is of a different color than you, gwilliy. But whatdoIknow?

Nate1 · #21 (**permalink**) 03-04-08, 02:08

Thanks a lot worked very well, I inserted the typ to make defining which table the row came from easier for locating the images folder,

SELECT fThumbNail AS sImage,'L' as typ,
iListingID AS ID,iListingID AS Parent,sTitle,sDescription FROM Listing UNION ALL

SELECT sImage as sImage,'D' as typ,ParentID AS Parent,WikiID as
ID,sTitle,sDescription FROM wikidata UNION ALL

SELECT (SELECT sImage FROM (SELECT TOP 1 NewID() x,sImage
FROM
wiki_images wi WHERE wi.wikiid = wikiinfo.parentid ORDER BY x) as sImage) as sImage,'W' as typ
,wikiinfo.ParentID AS Parent,wikiinfo.WikiInfoID AS ID, stitle,sdescription FROM wikiinfo ORDER BY stitle

GWilliy · #22 (**permalink**) 03-04-08, 06:13

Your welcome Nate - Glad to help

GW

blindman · #23 (**permalink**) 03-04-08, 09:29

Okay, well one of your nested subqueries is completely superfluous. Your statement can be rewritten more simply as:

Code:

SELECT	fThumbNail AS sImage,
	'L' as typ,
	iListingID AS ID,
	iListingID AS Parent,
	sTitle,
	sDescription
FROM	Listing
UNION ALL
SELECT	sImage as sImage,
	'D' as typ,
	ParentID AS Parent,
	WikiID as ID,
	sTitle,
	sDescription
FROM	wikidata
UNION ALL
SELECT	(SELECT	TOP 1 sImage
		FROM	wiki_images wi
		WHERE	wi.wikiid = wikiinfo.parentid
		ORDER BY newid()) as sImage,
	'W' as typ,
	wikiinfo.ParentID AS Parent,
	wikiinfo.WikiInfoID AS ID,
	stitle,sdescription
FROM	wikiinfo
ORDER BY stitle

...and, though Nate is apparently uninterested in such concepts as efficiency or best practices, for the benefit of others who stumble upon this thread I'll post the proper method of coding this, which avoids bucketloads of unnecessary table scans:

Code:

SELECT	fThumbNail AS sImage,
	'L' as typ,
	iListingID AS ID,
	iListingID AS Parent,
	sTitle,
	sDescription
FROM	Listing
UNION ALL
SELECT	sImage as sImage,
	'D' as typ,
	ParentID AS Parent,
	WikiID as ID,
	sTitle,
	sDescription
FROM	wikidata
UNION ALL
SELECT	Images.sImage,
	'W' as typ,
	wikiinfo.ParentID AS Parent,
	wikiinfo.WikiInfoID AS ID,
	stitle,
	sdescription
FROM	wikiinfo
	left outer join --Images
		(SELECT	ROW_NUMBER() OVER (PARTITION BY wikiinfo.parentid ORDER BY NewID()) as ROWNUMBER,
			wiki_images.wikiid,
			wiki_images.sImage as sImage
		from	wiki_images) Images
		on wikiinfo.parentid = Images.wikiid
		and Images.ROWNUMBER = 1
ORDER BY stitle

Nate1 · #24 (**permalink**) 03-04-08, 14:07

Blindman So by superfluous you mean the three selects in the one statement is unnecessary?

Ill try the bottom query tonight also, I would like to see the difference in speed Ive seen it done in SQL2005 Management Studio where the reports are created for each query requested but could you tell me where to click so I can see the difference between these queries?

GWilliy · #25 (**permalink**) 03-04-08, 14:57

Eeeek

But

1] Ref the superfluous subquery

Code:


Msg 104, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a UNION operator.

2] Is'nt it Best Practice to leave behind easily maintainable Code

3] Why do you think there will be table scans as the only join is in the subquery and thats via the id which is probably a clustererd PK integer.

4] Who gets to spend valuable minutes nowadays optimizing queries that probably have insignificant performance or system overheads.

I don't mean to be bolchy blindman, I know you can run rings round me with TSQL and I have a lot of respect for all you guys.

Nate I'd be interested to see how many seconds each version takes to run on average (after running each a couple of times first for queryplan caching etc.)

Now I'm gonna duck

GW

Internet Services

Web Development

Digital Marketing

Consumer Tech