Self join query problem - 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

Other Forums

MS Access

Self join query problem

Author

Topic

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2003-11-16 : 05:13:25

Hi helper
Here is a query I created all from one table:

SELECT distinct ML1.Caption AS English,ML2.Caption AS Spanish FROM MULTI_LANGUAGE_CAPTION AS ML1,MULTI_LANGUAGE_CAPTION AS ML2 where ML1.CaptionID = ML2.CaptionID and ML1.LanguageID = 1 and ML2.LanguageID=2

if MULTI_LANGUAGE_CAPTION is:
LanguageID CaptionID Caption
1 1 English:Product1
1 1 English:Product2
1 1 English:Product3
2 1 Spanish:Product1
2 1 Spanish:Product2
2 1 Spanish:Product3

the result i will see on a grid is:

English Spanish

English:Product1 Spanish:Product1
English:Product2 Spanish:Product2
English:Product3 Spanish:Product3

My problem is, if my table is :
LanguageID CaptionID Caption
1 1 English:Product1
1 1 English:Product2
1 1 English:Product3
2 1 Spanish:Product1

How can the result be:

English Spanish

English:Product1 Spanish:Product1
English:Product2 null
English:Product3 null

please advise
Jo

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-16 : 07:47:57

SELECT ML1.Caption AS English, ML2.Caption AS Spanish
FROM
(SELECT * FROM MULTI_LANGUAGE_CAPTION WHERE LanguageID = 1) AS ML1
LEFT JOIN
(SELECT * FROM MULTI_LANGUAGE_CAPTION WHERE LanguageID = 2) AS ML2
ON
((ML1.CaptionID = ML2.CaptionID) AND
(RIGHT(ML1.Caption, 1) = RIGHT(ML2.Caption, 1)))

UNION ALL

SELECT ML1.Caption AS English, ML2.Caption AS Spanish
FROM
(SELECT * FROM MULTI_LANGUAGE_CAPTION WHERE LanguageID = 1) AS ML1
RIGHT JOIN
(SELECT * FROM MULTI_LANGUAGE_CAPTION WHERE LanguageID = 2) AS ML2
ON
((ML1.CaptionID = ML2.CaptionID) AND
(RIGHT(ML1.Caption, 1) = RIGHT(ML2.Caption, 1)))

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2003-11-16 : 09:16:46

Hi Stoad,
sims like you got the solution. Now I have a continuing problem.
I gave the example for simplification. In the real world I ave 4 tables involved. I will show you all and I hope you can help me with all the inner joins hell.
CAPTION table:
ID Description
1 Caption1
2 Caption2
3 Caption3

CAPTION_IN_COMPONENTS table(has reference to Caption)
ComponentID | CaptionID
1 1
1 2
2 1
2 3

MULTI_LANGUAGE_CAPTION table (has reference to Caption)
LanguageID | CaptionID | Text
1 1 English:sssss
1 2 English:ddddd
1 3 English:fffff
2 1 Spanish:ccccc
2 3 Spanish:AAAAA

The only thing I have is ComponentID.

I need to display on a grid (ex.ComponentID = 1), using one query the following:

ML_CAPTION.Text | Caption.Description | ML_CAPTION.Text
English:sssss | Caption1 | Spanish:ccccc
English:ddddd | Caption2 |

Your query is good but trying to add the 2 tables above caused me problems.
Please advise
Thanks alot.

quote:
Originally posted by Stoad

SELECT ML1.Caption AS English, ML2.Caption AS Spanish
FROM
(SELECT * FROM MULTI_LANGUAGE_CAPTION WHERE LanguageID = 1) AS ML1
LEFT JOIN
(SELECT * FROM MULTI_LANGUAGE_CAPTION WHERE LanguageID = 2) AS ML2
ON
((ML1.CaptionID = ML2.CaptionID) AND
(RIGHT(ML1.Caption, 1) = RIGHT(ML2.Caption, 1)))

UNION ALL

SELECT ML1.Caption AS English, ML2.Caption AS Spanish
FROM
(SELECT * FROM MULTI_LANGUAGE_CAPTION WHERE LanguageID = 1) AS ML1
RIGHT JOIN
(SELECT * FROM MULTI_LANGUAGE_CAPTION WHERE LanguageID = 2) AS ML2
ON
((ML1.CaptionID = ML2.CaptionID) AND
(RIGHT(ML1.Caption, 1) = RIGHT(ML2.Caption, 1)))

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-16 : 14:16:06

SELECT ML1.Text AS English, ML1.Description, ML2.Text AS Spanish
FROM
(SELECT t2.CaptionID, t2.Description, t1.Text FROM
MULTI_LANGUAGE_CAPTION as t1, CAPTION as t2, CAPTION_IN_COMPONENTS as t3
WHERE t1.LanguageID=1 and t1.CaptionID=t2.CaptionID and
t2.CaptionID=t3.CaptionID and t3.ComponentID=1) AS ML1
LEFT JOIN
(SELECT t2.CaptionID, t2.Description, t1.Text FROM
MULTI_LANGUAGE_CAPTION as t1, CAPTION as t2, CAPTION_IN_COMPONENTS as t3
WHERE t1.LanguageID=2 and t1.CaptionID=t2.CaptionID and
t2.CaptionID=t3.CaptionID and t3.ComponentID=1) AS ML2
ON
(ML1.CaptionID=ML2.CaptionID)
UNION ALL
SELECT ML1.Text AS English, ML1.Description, ML2.Text AS Spanish
FROM
(SELECT t2.CaptionID, t2.Description, t1.Text FROM
MULTI_LANGUAGE_CAPTION as t1, CAPTION as t2, CAPTION_IN_COMPONENTS as t3
WHERE t1.LanguageID=1 and t1.CaptionID=t2.CaptionID and
t2.CaptionID=t3.CaptionID and t3.ComponentID=1) AS ML1
RIGHT JOIN
(SELECT t2.CaptionID, t2.Description, t1.Text FROM
MULTI_LANGUAGE_CAPTION as t1, CAPTION as t2, CAPTION_IN_COMPONENTS as t3
WHERE t1.LanguageID=2 and t1.CaptionID=t2.CaptionID and
t2.CaptionID=t3.CaptionID and t3.ComponentID=1) AS ML2
ON
(ML1.CaptionID=ML2.CaptionID)

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2003-11-17 : 01:43:08

Thanks a lot,
It works great
cheers
Jo

quote:
Originally posted by Stoad

SELECT ML1.Text AS English, ML1.Description, ML2.Text AS Spanish
FROM
(SELECT t2.CaptionID, t2.Description, t1.Text FROM
MULTI_LANGUAGE_CAPTION as t1, CAPTION as t2, CAPTION_IN_COMPONENTS as t3
WHERE t1.LanguageID=1 and t1.CaptionID=t2.CaptionID and
t2.CaptionID=t3.CaptionID and t3.ComponentID=1) AS ML1
LEFT JOIN
(SELECT t2.CaptionID, t2.Description, t1.Text FROM
MULTI_LANGUAGE_CAPTION as t1, CAPTION as t2, CAPTION_IN_COMPONENTS as t3
WHERE t1.LanguageID=2 and t1.CaptionID=t2.CaptionID and
t2.CaptionID=t3.CaptionID and t3.ComponentID=1) AS ML2
ON
(ML1.CaptionID=ML2.CaptionID)
UNION ALL
SELECT ML1.Text AS English, ML1.Description, ML2.Text AS Spanish
FROM
(SELECT t2.CaptionID, t2.Description, t1.Text FROM
MULTI_LANGUAGE_CAPTION as t1, CAPTION as t2, CAPTION_IN_COMPONENTS as t3
WHERE t1.LanguageID=1 and t1.CaptionID=t2.CaptionID and
t2.CaptionID=t3.CaptionID and t3.ComponentID=1) AS ML1
RIGHT JOIN
(SELECT t2.CaptionID, t2.Description, t1.Text FROM
MULTI_LANGUAGE_CAPTION as t1, CAPTION as t2, CAPTION_IN_COMPONENTS as t3
WHERE t1.LanguageID=2 and t1.CaptionID=t2.CaptionID and
t2.CaptionID=t3.CaptionID and t3.ComponentID=1) AS ML2
ON
(ML1.CaptionID=ML2.CaptionID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources