Need help with Union? Query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Need help with Union? Query

Author

Topic

sqlhelpee
Starting Member

7 Posts

Posted - 2008-06-11 : 18:23:08

Hi, long time lurker, first time poster.

I have 2 tables, one contains all user IDs on my website, USERS, and another table which contains all the related user IDs of those users who have opted to receive emails, EMAILS.

I want to select all the users from the first table (which means every user, so "SELECT userId FROM USERS") but I want to list the ones who have opted to receive emails first. So it would be something like:

SELECT userId FROM USERS ORDER BY (SELECT relatedId FROM EMAILS WHERE relatedID = userID)

Of course, you can't have a subselect in an order by, and I can't quite figure out if I need a UNION or something similar. UNION works, but I don't know how to ORDER the relatedIds in EMAILS before the other userIds.

Hope this makes sense, and TIA~!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-11 : 18:30:43

SELECT u.userId
FROM USERS u
LEFT OUTER JOIN EMAILS e
ON u.userID = e.relatedID
ORDER BY e.relatedID --not sure if you want ASC or DESC

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlhelpee
Starting Member

7 Posts

Posted - 2008-06-11 : 20:48:24

OK, you're awesome, and that worked. But in order to explain my problem I kinda watered down the original SQL. There is one more clause I need, which I am writing like this, but which isn't working:

SELECT u.userId
FROM USERS u, PREFERENCES s
LEFT OUTER JOIN EMAILS e ON u.userID = e.relatedID
WHERE (u.userVerify <> 'n' AND u.optionAgreed = 'y') AND (u.userId = s.relatedUserId AND s.thisSubject = 49)
ORDER BY e.relatedID DESC

Your SQL works great; it's when I add the WHERE clause that I get the error ... I am assuming I need a different kind of JOIN statement there, in place of the WHERE?

THANKS TARA!!! YOU ROCK!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-11 : 21:01:05

What error are you getting?

Could you describe in words what you want to do with preferences, so that we know what type of join to write?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlhelpee
Starting Member

7 Posts

Posted - 2008-06-12 : 04:41:47

The exact error is
"Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'u' does not match with a table name or alias name used in the query."

The PREFERENCES table is a third table which contains 2 things I need; a related user ID, and a column called thisSubject, which for the purposes of this example contains the number "49".

So normally I would do this lookup, which works:

SELECT u.userId
FROM USERS u, PREFERENCES s
WHERE (u.userVerify <> 'n' AND u.optionAgreed = 'y') AND (u.userId = s.relatedUserId AND s.thisSubject = 49)

But I want to add what you showed me, which is

LEFT OUTER JOIN EMAILS e ON u.userID = e.relatedID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-12 : 12:41:20

SELECT u.userId
FROM USERS u
INNER JOIN PREFERENCES s
ON u.userId = s.relatedUserId
LEFT OUTER JOIN EMAILS e
ON u.userID = e.relatedID
WHERE u.userVerify <> 'n' AND u.optionAgreed = 'y' AND s.thisSubject = 49
ORDER BY e.relatedID DESC

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlhelpee
Starting Member

7 Posts

Posted - 2008-06-12 : 15:54:18

Excellent, worked like a charm! Thanks again for all your help!!!!

sqlhelpee
Starting Member

7 Posts

Posted - 2008-06-22 : 09:29:26

OK, the query worked great, and all is good. But I have another request about this query. It currently selects all IDs of users who opted to receive emails first, and then the rest of the IDs. This is the SQL that works:

SELECT u.userId
FROM USERS u
INNER JOIN PREFERENCES s
ON u.userId = s.relatedUserId
LEFT OUTER JOIN EMAILS e
ON u.userID = e.relatedID
WHERE u.userVerify <> 'n' AND u.optionAgreed = 'y' AND s.thisSubject = 49
ORDER BY e.relatedID DESC

Now, I would like to now if it's possible via SQL to randomize each set. For example, I do want the e.relatedID to be listed before the other IDs, but I would like that group selected in random order, and the group of regular IDs selected in random order. Since there are 2 sets of returns, I don't know if you can randomize each set and still show the first set first.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-22 : 12:56:07

quote:
Originally posted by sqlhelpee

OK, the query worked great, and all is good. But I have another request about this query. It currently selects all IDs of users who opted to receive emails first, and then the rest of the IDs. This is the SQL that works:

SELECT u.userId
FROM USERS u
INNER JOIN PREFERENCES s
ON u.userId = s.relatedUserId
LEFT OUTER JOIN EMAILS e
ON u.userID = e.relatedID
WHERE u.userVerify <> 'n' AND u.optionAgreed = 'y' AND s.thisSubject = 49
ORDER BY e.relatedID DESC

Now, I would like to now if it's possible via SQL to randomize each set. For example, I do want the e.relatedID to be listed before the other IDs, but I would like that group selected in random order, and the group of regular IDs selected in random order. Since there are 2 sets of returns, I don't know if you can randomize each set and still show the first set first.

Can you illustrate this with some sample data so that its easy for us to understand what you're trying to achieve?

sqlhelpee
Starting Member

7 Posts

Posted - 2008-06-22 : 23:09:33

Here's some data:


USERS TABLE
userID	userVerify		optionAgreed
1	y		y
2	y		y
3	y		y
4	y		y

PREFERENCES TABLE
relatedUserId	thisSubject
1		49
2		49
3		49
4		49

EMAILS TABLE
relatedID
3
1

SO the sql query above will return the user IDs in this order:

3
1
2
4

The query selects ID numbers 3 and 1 first, because they are in both the Users table AND the Emails table. THen it selects ID 2 and 4 because they are only in the users table. Thanks to this query, IDs 3 and 1 will appear first in the list, which is what I want. But what I would like them to appear in random order, and I would also like the second set of returns, those IDS which are only in the users table (2 and 4) to also appear random. So the results may be returned as:

1
3
4
2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 01:44:12

[code]SELECT u.userId
FROM @USERS u
INNER JOIN @PREFERENCES s
ON u.userId = s.relatedUserId
LEFT OUTER JOIN (SELECT relatedId,1 AS Ord FROM @EMAILS) e
ON u.userID = e.relatedID
WHERE u.userVerify <> 'n' AND u.optionAgreed = 'y' AND s.thisSubject = 49
ORDER BY Ord DESC,NEWID()[/code]

sqlhelpee
Starting Member

7 Posts

Posted - 2008-06-23 : 12:28:37

Excellent, that worked!! Thank you very much!!

shijobaby
Starting Member

44 Posts

Posted - 2009-08-20 : 03:03:43

the column prefix does not match with a table name or alias name used in the query.

I was also working to solve this error

Nw i found why it is comming just needs to make small changes in query

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-messages-msg-107.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources