SQL Server Forums
Profile | Register | 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
 New Topic  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  
 New 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.16 seconds. Powered By: Snitz Forums 2000