Help! SQL select statement for an uncommon task

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 2008 Forums

Transact-SQL (2008)

Help! SQL select statement for an uncommon task

Author

Topic

j2bmw
Starting Member

3 Posts

Posted - 2011-01-13 : 20:14:32

Let’s say we have 3 tables: fruit name, color and taste that have data like this:

Fruits:
Apple

Color:
Red
Green
Yellow

Taste:
Sweet
Bitter

I need to come up with a single SQL statement that will combine all 3 tables so that for each fruit there will be as many records as max number of records in any of other tables (in this case 3 records because the Color table has 3 of them) and values will be in order how they appear in source tables like:

Result of a statement I can’t come up with:
Apple Red Sweet
Apple Green Bitter
Apple Yellow NULL

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-13 : 20:32:47

Homework?

Show us what you have so far.

I'll give you a hint. Review OUTER JOINs.

j2bmw
Starting Member

3 Posts

Posted - 2011-01-13 : 20:50:24

> Show us what you have so far.

The real system is a bit more complicated and doesn't use fruit analogy :) Statement that we have by now looks like this but fruits translation is a bit simpler to post:

SELECT SHARED_RIGHTS.ID_INDEX AS RIGHT_INDEX,

SHARED_RIGHTS.ID_SHARED_RIGHTS AS SHARED_RIGHTS_ID,

SHARED_RIGHTS.SHARED_RIGHTS_CUSTOM_NAME AS CUSTOM_NAME,

SharedrightsSchedule.ID_SCHEDULE AS SCHEDULE_ID,

AREA_RIGHTS_GROUP.ID_AREA_RIGHTS_GROUP,

DOOR_RIGHTS_GROUP.ID_DOOR_RIGHTS_GROUP,

FLOOR_RIGHTS_GROUP.ID_FLOOR_RIGHTS_GROUP,

MENU_RIGHTS_GROUP.ID_MENU_RIGHTS_GROUP

FROM SHARED_RIGHTS INNER JOIN

SharedrightsSchedule ON SHARED_RIGHTS.ID_INDEX = SharedrightsSchedule.ID_SHARED_RIGHTS INNER JOIN

AREA_RIGHTS_GROUP ON SHARED_RIGHTS.ID_INDEX = AREA_RIGHTS_GROUP.ID_SHARED_RIGHTS INNER JOIN

DOOR_RIGHTS_GROUP ON SHARED_RIGHTS.ID_INDEX = DOOR_RIGHTS_GROUP.ID_SHARED_RIGHTS INNER JOIN

FLOOR_RIGHTS_GROUP ON SHARED_RIGHTS.ID_INDEX = FLOOR_RIGHTS_GROUP.ID_SHARED_RIGHTS INNER JOIN

MENU_RIGHTS_GROUP ON SHARED_RIGHTS.ID_INDEX = MENU_RIGHTS_GROUP.ID_SHARED_RIGHTS INNER JOIN

PANELS ON SHARED_RIGHTS.ID_PANEL = PANELS.ID_PANEL AND PANELS.ID_PANEL='%1%'

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-14 : 02:22:00

@j2bmw, the analogy has something missing or might be i could not understand!!!!

---------------------------------------
Result of a statement I can’t come up with:
Apple Red Sweet
Apple Green Bitter
Apple Yellow NULL Why yellow has to be with Null? Can't Red or Green Be NULL or Can't Yellow be Sweet or Bitter. Is there any specific constraint for this?
---------------------------------------

Also, please note that you have used Inner Joins which will return only Matching rows ... So the ignore the Null part :D... By the way which of the tables in above code representing "Taste". :D Get that table at the bottom of all joins condition but with Left Join and check if it works for you!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-14 : 03:06:12

[code]
select fruit, color, taste
from (
select fruit, color, row_no = row_number() over (partition by fruit order by color)
from Fruits cross join Color
) fc
left join
(
select taste, row_no = row_number() over (order by taste)
from Taste
) t on fc.row_no = t.row_no
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources