Tricky Query in SQL Server - 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)

Tricky Query in SQL Server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-13 : 08:32:51

Sameer writes "Please Solve the same in MS Sql-Server with the help of SQL Query.

The Scenario is as follow.

Table A
=======
Code            Name

CE              Java
CE              Oracle
CE              Microsoft
AE              Sun
AE              Digital
AE              HP
AR              Compaq 

Table B
=======

Code            Location

CE              US
CE              UK
AE              Japan

Now with the help of SQL Query , Please Produce the output.

-----------------------------------------------------
Code            Name                Location
-----------------------------------------------------

CE              Java                 US
CE              Oracle               UK 
CE              Microsoft            NULL
AE              Sun                  Japan
AE              Digital              NULL 
AE              HP                   NULL
AR              Compaq               NULL

Common field is Code between Both the tables. But None of the tables has a Primary Key."

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-02-13 : 08:42:23

Is this a 'class project'?

never mind doing this in SQL-Query....try doing in on paper first!!!

from the data given, I don't think the output is achieveable (unless there are some other rules (or data) you haven't told us about). for instance, what rule is used to link "US" with "Java" or "UK" with "Oracle"?

from the data shown while "US" can be linked with "Java"...so too can "UK" be linked with "Java" (using the code field)....yet you don't show that combination in the output. Why?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-13 : 11:43:52

Isn't it obvious?

It's supposed to weld the two tables together on the values in the Code column, using the 'order' of the rows in the table.
Which part should we break to him gently? That joins don't work like that, or that rows in tables can't be ordered on anything except their content?

Should that actually be what you want to do, the last post in this thread may be of some help on the welding (or at least convince you of how awkward it is). The ordering you'll have to decide for yourself:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12379

Edited by - Arnold Fribble on 02/13/2002 11:53:51

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources