An interview question - tricky

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)

An interview question - tricky

Author

Topic

NickRice
Starting Member

13 Posts

Posted - 2003-08-31 : 09:32:47

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
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-08-31 : 11:35:09

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

NickRice
Starting Member

13 Posts

Posted - 2003-08-31 : 12:30:18

Thanks.

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

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-31 : 17:06:17

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.

NickRice
Starting Member

13 Posts

Posted - 2003-09-01 : 01:22:19

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 :)

NickRice
Starting Member

13 Posts

Posted - 2003-09-01 : 01:46:17

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

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-01 : 06:59:00

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...

NickRice
Starting Member

13 Posts

Posted - 2003-09-01 : 07:21:48

Thanks for your reply Stoad.

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

nandaiitm
Starting Member

1 Post

Posted - 2014-02-19 : 01:55:43

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources