Queryyyy Helpp - 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

Queryyyy Helpp

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-27 : 04:05:25

Table 1

code aname
----------- ----------
1 Cat
2 Dog
3 Bird
Table 2

code bname
----------- ----------
1 aaa
1 bbb
2 ccc
2 ddd
Table 3

code cname
----------- ----------
1 xxx
1 yyy
1 zzz
2 www
Required Output

code aname bname cname
----------- ---------- ---------- ----------
1 Cat aaa xxx
1 Cat bbb yyy
1 Cat NULL zzz
2 Dog ccc www
2 Dog ddd NULL
3 Bird NULL NULL

shaggy
Posting Yak Master

248 Posts

Posted - 2009-02-27 : 04:20:24

use outer join

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-27 : 04:21:14

I Tried It But .......
u say ig u have any perfect solution

quote:
Originally posted by shaggy

use outer join

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-27 : 05:18:14

[code]select a.code,b.bname,c.cname
from
table1 a
left join table2 b on a.id=b.id
left join table3 c on a.id=c.id [/code]

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-27 : 05:32:51

it gives the wrong output see it yourself.........
Thanks........

quote:

Originally posted by sakets_2000

select a.code,b.bname,c.cname
from
table1 a 
left join table2 b on a.id=b.id 
left join table3 c on a.id=c.id

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-02-27 : 06:19:39

This looks like a test to show you understand a table is an unordered set.

Something like the following will work, but you had better do some reading so you can explain why!

SELECT T1.code, T1.aname, D.bname, D.cname
FROM Table1 T1
LEFT JOIN
(
    SELECT COALESCE(D2.code, D3.code) AS code
        ,D2.bname
        ,D3.cname
    FROM
    (
        SELECT ROW_NUMBER() OVER (PARTITION BY T2.code ORDER BY T2.bname) AS RowID
            ,T2.*
        FROM Table2 T2
    ) D2
        FULL JOIN
        (
            SELECT ROW_NUMBER() OVER (PARTITION BY T3.code ORDER BY T3.cname) AS RowID
                ,T3.*
            FROM Table3 T3
        ) D3
            ON D2.code = D3.code
                AND D2.RowId = D3.RowId
) D
    ON T1.code = D.code

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-27 : 07:18:10

Thanks You Ver Much Sir.....
Yes It Can Solved My problem,,,,,,
Actually It is like a Test Mean I read it Some where and i also tried it so many times so i cant get succeed so i just want to know that how this can be done?
I only want to know the logic behind this,,,,,,
well Thanks You?

quote:

Originally posted by Ifor

This looks like a test to show you understand a table is an unordered set.

Something like the following will work, but you had better do some reading so you can explain why!

SELECT T1.code, T1.aname, D.bname, D.cname
FROM Table1 T1
LEFT JOIN
(
    SELECT COALESCE(D2.code, D3.code) AS code
        ,D2.bname
        ,D3.cname
    FROM
    (
        SELECT ROW_NUMBER() OVER (PARTITION BY T2.code ORDER BY T2.bname) AS RowID
            ,T2.*
        FROM Table2 T2
    ) D2
        FULL JOIN
        (
            SELECT ROW_NUMBER() OVER (PARTITION BY T3.code ORDER BY T3.cname) AS RowID
                ,T3.*
            FROM Table3 T3
        ) D3
            ON D2.code = D3.code
                AND D2.RowId = D3.RowId
) D
    ON T1.code = D.code

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-02-27 : 09:12:55

The outline logic is as follows:

The obvious problem is the join between Table2 and Table3.
The only column that can be joined on is code and this will produce more results
than required.

eg. The following will produce four rows instead of the required two:

DECLARE @t2 TABLE
(
    code int NOT NULL
    ,bname varchar(20) NOT NULL
)
INSERT INTO @t2
SELECT 1, 'aaa' UNION ALL
SELECT 1, 'bbb'

DECLARE @t3 TABLE
(
    code int NOT NULL
    ,cname varchar(20) NOT NULL
)
INSERT INTO @t3
SELECT 1, 'xxx' UNION ALL
SELECT 1, 'yyy'

SELECT *
FROM @t2 T2
    JOIN @t3 T3
        ON T2.code = T3.code

Somehow you have to restrict the four rows to the two you want.
The thing that links bname to cname is the alphabeltic order so this
has to be obtained. The easiest way to do this is to create derived tables
and generate a RowId using the ROW_NUMBER() function. Once you have the RowIds
in the derived tables you can join on code and RowId to get the desired rows.

SELECT *
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY T2.code ORDER BY T2.bname) AS RowID
        ,*
    FROM @t2 T2
) D2
    JOIN
    (
        SELECT ROW_NUMBER() OVER (PARTITION BY T3.code ORDER BY T3.cname) AS RowID
            ,*
        FROM @t3 T3
    ) D3
        ON D2.code = D3.code
            AND D2.RowId = D3.RowId

The rest is easy.
1. As the number of rows for each code is different in Table2 and Table3 a FULL OUTER JOIN
instead of an INNER JOIN is required.
2. As a FULL JOIN is being used the COALESCE, or ISNULL, function will ensure a single code
is returned.
3. Wrap the lot as a derived table, D, and join to Table1.

(Personally, I would normally RIGHT JOIN to Table1 instead of creating the derived table D
but I thought you would find the LEFT JOIN to the derived table easier to understand.)

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-27 : 10:34:41

but as u noe row_number() function is only available for Sql 2005 or later ,,,so
wat would happn if it is in i tried to generate row number in dis in sql 200

but u noe in tablec the row numbers which get generated is like dis

SELECT e.cname,e.code,
(SELECT COUNT(*) FROM tablec e2 WHERE e2.cname <= e.cname) AS rownumber
FROM tablec e

select * from tablec

But it gives me wrong row_numbers
so how to do this in Sql 2000

sry but i jus want to knw the logic behind dis

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-02-27 : 11:42:38

(SELECT COUNT(*) FROM tablec e2 WHERE e2.code = e.code AND e2.cname <= e.cname) AS rownumber

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-28 : 09:21:43

Thanks Sir,,,
for ur help i dunn have an Sql Box noe but m sre it will work i noe that?
Thanks Again...........

quote:
Originally posted by Ifor

(SELECT COUNT(*) FROM tablec e2 WHERE e2.code = e.code AND e2.cname <= e.cname) AS rownumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources