tricky query 
Author Message
 tricky query

I have two tables.... (NINE and TEN) The data is as shown
below.....
NINE
key1       score      
---------- ----------
1          5        
2          10        
5          12        
6          16        

TEN
key1       score      
---------- ----------
1          2        
3          8        
7          10        
9          12  

I need a query that will return this...
key1       scoreNINE ScoreTEN
---------- --------- ---------
1          5         2
2          10        NULL
3          NULL      8
5          12        NULL
6          16        NULL
7          NULL      10
9          NULL      12

This is probably simple, but I am stuck...Thanks
Note:This is also posted in
microsoft.public.sqlserver.server.  Please only reply to
that newsgroup



Sun, 22 Feb 2004 00:04:41 GMT
 tricky query

With Tammy's! and Scott's help I figured it out..
SELECT
    COALESCE (Nine.Key1, Ten.Key1) AS KEY1,
    Nine.Score AS ScoreNINE,
    Ten.Score AS ScoreTEN
FROM
    Ten FULL OUTER JOIN
                      Nine ON Ten.Key1 = Nine.Key1
Quote:
>-----Original Message-----
>I have two tables.... (NINE and TEN) The data is as shown
>below.....
>NINE
>key1       score      
>---------- ----------
>1          5        
>2          10        
>5          12        
>6          16        

>TEN
>key1       score      
>---------- ----------
>1          2        
>3          8        
>7          10        
>9          12  

>I need a query that will return this...
>key1       scoreNINE ScoreTEN
>---------- --------- ---------
>1          5         2
>2          10        NULL
>3          NULL      8
>5          12        NULL
>6          16        NULL
>7          NULL      10
>9          NULL      12

>This is probably simple, but I am stuck...Thanks
>Note:This is also posted in
>microsoft.public.sqlserver.server.  Please only reply to
>that newsgroup
>.



Sun, 22 Feb 2004 03:32:02 GMT
 tricky query
Here is the answer

select X.key1,N.score,T.score from
(select key1 from NINE union
select key1 from TEN) X
left outer JOIN NINE N
on X.key1 = N.key1
left outer JOIN TEN T
on X.key1 = T.key1
order by X.key1

Hai Nguyen


Quote:
> I have two tables.... (NINE and TEN) The data is as shown
> below.....
> NINE
> key1       score
> ---------- ----------
> 1          5
> 2          10
> 5          12
> 6          16

> TEN
> key1       score
> ---------- ----------
> 1          2
> 3          8
> 7          10
> 9          12

> I need a query that will return this...
> key1       scoreNINE ScoreTEN
> ---------- --------- ---------
> 1          5         2
> 2          10        NULL
> 3          NULL      8
> 5          12        NULL
> 6          16        NULL
> 7          NULL      10
> 9          NULL      12

> This is probably simple, but I am stuck...Thanks
> Note:This is also posted in
> microsoft.public.sqlserver.server.  Please only reply to
> that newsgroup



Wed, 25 Feb 2004 04:56:37 GMT
 tricky query
Its a little late though!
But here's my version of it :-)

SELECT A.key1, A.Score ScoreNine, B.Score ScoreTwo
FROM Nine A, Two B
WHERE A.key1 *= B.key1
UNION
SELECT B.key1, A.Score ScoreNine, B.Score ScoreTwo
FROM Nine A, Two B
WHERE A.Key1 =* B.Key1

Thanks,
Srikanth.

--
Dear Sir/Madam!
I am looking for openings in the area of Data warehousing, Client-server or
Web development areas. I have over 4 years of industry experience, during
which I executed a number of projects. I have been working extensively in
data warehousing projects and successfully implemented solutions for the
clients, using SQL Server 2000, Analysis Services, and MDX.  My job profile
also includes analysis, design and development of web sites and software
applications. My other skillsets include  Visual Basic, COM, MTS, IIS, ASP
(Active Server Pages), VBScript, XML, XSL, Javascript, SQL Server, Windows
NT and HTML. I have over 3 years of experience with ASP, VB and SQL Server.

I am attaching my resume for your consideration. I am currently on a H1-B
Visa.
My contact number is 818-4729664 (Cell). Please feel free to call me in case
my skills match your requirements.

Best regards,
Srikanth Vishnubhotla.

Quote:
> I have two tables.... (NINE and TEN) The data is as shown
> below.....
> NINE
> key1       score
> ---------- ----------
> 1          5
> 2          10
> 5          12
> 6          16

> TEN
> key1       score
> ---------- ----------
> 1          2
> 3          8
> 7          10
> 9          12

> I need a query that will return this...
> key1       scoreNINE ScoreTEN
> ---------- --------- ---------
> 1          5         2
> 2          10        NULL
> 3          NULL      8
> 5          12        NULL
> 6          16        NULL
> 7          NULL      10
> 9          NULL      12

> This is probably simple, but I am stuck...Thanks
> Note:This is also posted in
> microsoft.public.sqlserver.server.  Please only reply to
> that newsgroup



Mon, 03 May 2004 08:12:08 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. tricky query?

2. tricky query

3. A tricky query question...

4. Tricky Query help

5. tricky query

6. Tricky query (for me anyways)

7. Need help with a tricky query

8. HELP: Tricky query....

9. Tricky query for me, should be simple for you

10. tricky query (for me atleast)

11. Tricky query question

12. Tricky query question.


 
Powered by phpBB® Forum Software