sql server new users: Query Q: -- sql server new users

Re: Query Q:

Andrew J. Kelly
8/26/2007 2:47:45 PM

sql server new users: UNION by itself removes duplicates where as the ALL clause includes them.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

[quoted text, click to view]

Re: Query Q:

TheSQLGuru
8/26/2007 5:13:02 PM

This is a VERY important thing to remember too!! Not only does it affect
your output, but it affects performance too! UNION does a SORT and DISTINCT
(both expensive operations) under the covers.

--
TheSQLGuru
President
Indicium Resources, Inc.

[quoted text, click to view]

Query Q:

Ana
8/26/2007 6:27:09 PM

Hi,
The following query lists 21786 records when UNION ALL is used and 19015
records when UNION is used.
In both cases, only LASTNAMEs are displayed. What am I doing wrong? I'm
playing with UNION to understand its use so I can combine several queries
into one.
TIA
Ana

SELECT LASTNAME
FROM CLIENTS
UNION
SELECT NAME FROM CLIENTS WHERE LASTNAME LIKE %SON%
ORDER BY LASTNAME

Re: Query Q:

Ana
8/27/2007 12:00:00 AM

Thank you.
So what's best practice to combine several queries into one?
Ana

"Ana" <nospam@yahoo.com> escribió en el mensaje de noticias
news:u$aCC4$5HHA.1168@TK2MSFTNGP02.phx.gbl...
[quoted text, click to view]

Re: Query Q:

Andrew J. Kelly
8/27/2007 12:00:00 AM

You really need to give some more details on exactly what you are trying to
accomplish. How you do something often depends mostly on what the goal is
and the schemas involved.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

[quoted text, click to view]

Re: Query Q:

Andrew J. Kelly
8/27/2007 1:17:05 PM

The structures of those 3 tables are completely different as is the
information in them. With UNION you need to have the same number of columns
with similar datatypes in each of the tables you are unioning. I believe
what you want to do is join them not union them. If there isn't at least a
1 - 1 match you can use outer joins. I suggest you get a good book on SQL
and read up on the different techniques to decide what you want to do. Here
are some I would recommend to anyone, especially one who has questions like
yours which can be answered in TSQL Querying:

http://www.sql.co.il/books/insidetsql2005/

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

[quoted text, click to view]

Re: Query Q:

Ana
8/27/2007 6:05:37 PM

Well, for instance, I have tblClient, tblClaim, tblAgent.
In a query I want to combine the sells from tblClient, the claims from the
same client stored in tblClaims produced by an agent. Now, not all policies
in tblClient have had claims. Not sure if I explained myself. Bottom line,
combine several tables into one query.
TIA
Ana

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> escribió en el mensaje de
noticias news:Ow6dUhK6HHA.5360@TK2MSFTNGP03.phx.gbl...
[quoted text, click to view]

Re: Query Q:

TheSQLGuru
8/29/2007 12:00:00 AM

Sounds like Ana is more in need of a beginners Transact SQL book, like the
one from WROX.

--
TheSQLGuru
President
Indicium Resources, Inc.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.