How to JOIN this mess and then reorder results? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

How to JOIN this mess and then reorder results?

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-08-30 : 16:03:49

I have one table that contains demographic information/brand name preferences on people who are related to one another (brother/sister). The company decided long ago on a primary key numbering system that tags the family relationship like this: 1 is a unique family number, 1.1 is the male, 1.2 is the female; 2 is the next unique family number, 2.1 the male, 2.2 the female, etc.

I want to run a query that extracts related family members based on their unique family IDs as noted above. Then I want to display the results of this query in the fashion below:

COLUMN HEADINGS:

Male_Relation/ Female_Relation/Male_Brand/Female_Brand

ROW RESULTS:
Joe Smith / Joan Smith/ Timberland/ Gucci

I'm envisioning a query that splits the main table in two by male/female, then a JOIN somehow on common family number, followed by a reordering of the returned columns so they present as above, but I'm basically hitting the wall here. Ideas?

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-08-30 : 17:39:12

Smack the DBA that designed that database??

If you can figure ou how to break the data into a table similar to mine, you are golden methinks.


CREATE TABLE #Steel(FamilyID INT, GenderID INT , HumanID VARCHAR(50), Brand VARCHAR(50))

INSERT INTO #Steel(FamilyID, GenderID, HumanID, Brand) VALUES(1,1, '1.1', 'TimberLand')
INSERT INTO #Steel(FamilyID, GenderID, HumanID, Brand) VALUES(1,2, '1.2', 'Gucci')
INSERT INTO #Steel(FamilyID, GenderID, HumanID, Brand) VALUES(2,1, '2.1', 'Tommy')
INSERT INTO #Steel(FamilyID, GenderID, HumanID, Brand) VALUES(2,2, '2.2', 'Liz clairbone')
INSERT INTO #Steel(FamilyID, GenderID, HumanID, Brand) VALUES(3,1, '3.1', 'Levi')
INSERT INTO #Steel(FamilyID, GenderID, HumanID, Brand) VALUES(3,2, '3.2', 'Walmart')

SELECT m.HumanID, m.Brand, f.HumanID, f.Brand
FROM #Steel m
INNER JOIN #Steel f on f.FamilyID = m.FamilyID 
WHERE m.GenderID = 1
AND f.GenderID = 2

DROP TABLE #Steel

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-31 : 06:48:08

I don't understand this.
Can you only have one male and one female sibling per 'family'?

Edited by - Arnold Fribble on 08/31/2002 06:53:01

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-09-03 : 12:26:36

I agree, seems kinda unrealistic considering the real world.

I hope my "solution" works for ya.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources