Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 An interview question - tricky
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NickRice
Starting Member

13 Posts

Posted - 08/31/2003 :  09:32:47  Show Profile  Reply with Quote
A guy on interview panel asked me this tricky question regarding queries/joins (infact it the only scenario-based question I was asked). I couldn't figure it out myself so I thought of discussing it here.

I was presented with the following situation: there are two tables "T1" and "T2". T1 has two fields (RowID - PK, RowName - VarChar). T2 has three fields (FKRowID - FK, FieldName - VarChar, FieldValues - Varchar). Now, I was asked to write a SQL query to display records for each T1.RowName with each related T2.FieldName and further with each related T2.FieldValue.

Example:

T1.RowID - T1.RowName
---------------------
1 - Apple

T2.FKRowID - T2.FieldName - T2.FieldValues
------------------------------------------
1 - Color - Red,Green,Yellow
1 - Taste - Sweet,Sour

ResultSet
-----------------------------------------------------
1 - Apple - Color - Red
1 - Apple - Color - Green
1 - Apple - Color - Yellow
1 - Apple - Taste - Sweet
1 - Apple - Taste - Sour


Any clues guys :)

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 08/31/2003 :  11:35:09  Show Profile  Reply with Quote
You would have to create a table that looks like this:

1 - Color - Red
1 - Color - Green
1 - Color - Yellow
1 - Taste - Sweet
1 - Taste - Sour

See this article.
http://www.sqlteam.com/item.asp?ItemID=2652

Once you had a table like that (let's call it TableX) then.

SELECT A.RowID, A.RowName, B.FieldName, B.FieldValue
FROM T1 A INNER JOIN TableX B On A.RowID = B.FKRowID

Go to Top of Page

NickRice
Starting Member

13 Posts

Posted - 08/31/2003 :  12:30:18  Show Profile  Reply with Quote
Thanks.

I just tried it. It solves the problem to the root! A little complicated though.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 08/31/2003 :  17:06:17  Show Profile  Visit nr's Homepage  Reply with Quote
I suspect they just wanted you to say that this is a bad structure and should be corrected.

Easiest way is just to create a temp table with the normalised data from T2 - just call charindex for the max number of entries in any string - saving the postion of that comma for every entry. Then just join the resulting table to the others. That's probably what I would do if I have to write something that would work in an interview.
The resultset format - a single string is a bit odd too.

Maybe they were looking for you to suggest a table valued function?

When I interview I will ask questions just to see if the candidate recognises there is a problem - whether or not they can solve it (if they can't then they can ask someone as long as they are willing to admit they don't know).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 08/31/2003 17:32:23
Go to Top of Page

NickRice
Starting Member

13 Posts

Posted - 09/01/2003 :  01:22:19  Show Profile  Reply with Quote
I agree nr. I guess they were trying to make me figure out the problem with the scenario rather than the solution itself. The first thing I suggested was to normalize the table.

Anyways, I didn't get the job. My SQL Server knowledge and skills do need an improvement :)
Go to Top of Page

NickRice
Starting Member

13 Posts

Posted - 09/01/2003 :  01:46:17  Show Profile  Reply with Quote
Just wondering on a variation of the original question:

How about if a nested listing was to be displayed of the same table date/structure?

Example:

T2.RowID - T2.FieldValues
------------------------------------------
1 - Red,Green,Yellow
2 - Sweet,Sour
3 - Small,Medium,Large

ResultSet
-----------------------------------------------------
Red - Sweet - Small
Red - Sweet - Medium
Red - Sweet - Large
Red - Sour - Small
Red - Sour - Medium
Red - Sour - Large
Green - Sweet - Small
Green - Sweet - Medium
Green - Sweet - Large
Green - Sour - Small
Green- Sour - Medium
Green - Sour - Large
Yellow - Sweet - Small
Yellow - Sweet - Medium
Yellow - Sweet - Large
Yellow - Sour - Small
Yellow - Sour - Medium
Yellow - Sour - Large

Edited by - NickRice on 09/01/2003 05:22:21
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 09/01/2003 :  06:59:00  Show Profile  Visit Stoad's Homepage  Reply with Quote
Transform this:
1 - Red,Green,Yellow
2 - Sweet,Sour
3 - Small,Medium,Large

into table t:
n___m
-------
1 - Red
1 - Green
1 - Yellow
2 - Sweet
2 - Sour
3 - Small
3 - Medium
3 - Large

select t.m, tt.m, ttt.m from t, t tt, t ttt
where
t.n < tt.n and tt.n < ttt.n

Oops... I've failed to use cross joins...
Go to Top of Page

NickRice
Starting Member

13 Posts

Posted - 09/01/2003 :  07:21:48  Show Profile  Reply with Quote
Thanks for your reply Stoad.

Can I improve on this query to resolve: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28826
Go to Top of Page

nandaiitm
Starting Member

1 Posts

Posted - 02/19/2014 :  01:55:43  Show Profile  Reply with Quote
create table testmulti1 (RowID int primary key, RowName VarChar(32))
create table testmulti2 (FKRowID int , FieldName VarChar(32), FieldValues Varchar(32))

insert into testmulti1(RowID,RowName)
values (1,'apple')

insert into testmulti2(FKRowID,FieldName,FieldValues)
select 1,'Color','Red,Green,Yellow' union all
select 1,'Taste','sweet,sour'


Query:
select FKRowID ,c.RowName, fieldname,items from dbo.testmulti2 a cross apply dbo.Split_v1(a.fieldvalues,',') b
inner join dbo.testmulti1 c on c.RowID=a.FKRowID
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.21 seconds. Powered By: Snitz Forums 2000