February 14, 2011 at 2:37 pm
Hello all,
I have been presented with what sounds like a very easy question. But, I have been away from SQL for a long time, and it is really challenging me.
Given a table with 3 columns - PersonID, Name and Gender - and 4 rows of data for 2 men and 2 women (Fred, Peter, Lisa, and Pam), how would I write a T-SQL statement to select distinct male-female pairs, like
Fred, Lisa
Fred, Pam
Peter, Lisa
Peter, Pam
..???
I am sure that it requires some type of temp table, or maybe some kind of nested loop, but I just can't seem to get it. If it was in C#, I would have been done in like 2 minutes. However, I am having trouble transferring that knowledge to T-SQL. Any help would be greatly appreciated.
Thank you : )
quantum kev
February 14, 2011 at 3:00 pm
kev this really looks a little bit like a homework question, so I'll give some hints instead of a full answer, unless you confirm it's not homework 🙂
what you are looking for is a "Cartesian Join" or "CROSS JOIN";
that will take every generate every possible group between two sets of data ; so if you have a table/select of just males and CROSS JOIN a table/select of just females, you get all possible combinations.
let me know if that gets you going in the right direction.
Lowell
February 14, 2011 at 3:01 pm
Check this rough draft....Hope it will be helpful
--CREATE TABLE dbo.PersonInfo
--(
--PersonID INT IDENTITY(1,1),
--Name VARCHAR(20),
--Gender CHAR(1)
--)
--INSERT INTO dbo.PersonInfo (Name, Gender)
--SELECT 'Frank', 'M'
--UNION ALL
--SELECT 'Peter', 'M'
--UNION ALL
--SELECT 'Monalisa', 'F'
--UNION ALL
--SELECT 'Pamela','F'
CREATE TABLE #MALE
(Sno int identity(1,1),
Male_Name Varchar(20)
)
CREATE TABLE #FEMALE
(Sno int identity(1,1),
Female_Name Varchar(20)
)
INSERT INTO #MALE (Male_Name)
SELECT Name FROM dbo.PersonInfo
WHERE Gender ='M'
INSERT INTO #FEMALE (Female_Name)
SELECT Name FROM dbo.PersonInfo
WHERE Gender ='F'
--SELECT * FROM #MALE
--SELECT * FROM #FEMALE
DECLARE @Cnt int
DECLARE @Count int
SET @Count=1
WHILE (@Count <= (SELECT COUNT(*) FROM #MALE))
BEGIN
SET @Cnt=1
WHILE (@Cnt <=(SELECT COUNT(*) FROM #FEMALE))
BEGIN
SELECT (SELECT male_Name from #MALE where Sno=@Count), (SELECT Female_Name from #FEMALE where Sno=@Cnt)
set @Cnt=@Cnt+1
END
set @Count=@Count+1
END
DROP TABLE #MALE
DROP TABLE #FEMALE
Thanks,
Andy
February 14, 2011 at 3:12 pm
Lowell,
Thank you for the tip. This is actually not a homework question - it is an interview prep question. And as I said, I have been away from database for awhile, developing web apps in ASP.NET. The new position I am hoping to get will require that I do my own SP's, so I am trying to brush up.
quantum_kev
February 14, 2011 at 3:14 pm
ok in that case, here is a solid example;
With PersonInfo(PersonID,Name,Gender)
As
(
SELECT 1, 'Fred','M' UNION ALL
SELECT 42,'Lisa','F' UNION ALL
SELECT 99, 'Pam','F' UNION ALL
SELECT 8,'Peter','M'
),
MyGuys As
(
SELECT Name FROM PersonInfo
WHERE Gender ='M'
),
MyGals As
(
SELECT Name FROM PersonInfo
WHERE Gender ='F'
)
SELECT * FROM MyGuys CROSS JOIN MyGals
Lowell
February 14, 2011 at 4:04 pm
Lowell,
Thank you so much for the tip. Ok - given the fact that I have a pre-existing table called "Persons", I created this SP based on what you posted :
ALTER PROCEDURE SelectPeople
AS
DECLARE @tmpMen TABLE (Name varchar)
insert into @tmpMen(Name)
select Name
from [dbo].[Persons]
where Gender = 'Male'
DECLARE @tmpWomen TABLE (Name varchar)
insert into @tmpWomen(Name)
select Name
from [dbo].[Persons]
where Gender = 'Female'
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
SELECT * FROM @tmpMen CROSS JOIN @tmpWomen
END
GO
However, when I execute it, I get nothing back - any ideas why?
Again, thank you so much for your help. It is hard for me sometimes to go from app code (C#, ASP.NET, etc) to T-SQL on a momen't notice, as there is a different mindset necessary. And unfortunately, I don't have the time needed to get into that 'mindset' this time! Lol
quantum_kev
February 14, 2011 at 4:22 pm
As a silly question, since you're not getting results, can you double check something?
select Name
from [dbo].[Persons]
where Gender IN ('Male', 'Female')
Does this return anything?
The code itself looks fine offhand.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 14, 2011 at 6:19 pm
Kev,
Your table def for Person that you posted had Gender as CHAR(1) - should you be using 'M' and 'F' instead of Male and Female?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 14, 2011 at 8:26 pm
Craig and Mister Magoo (Lol),
Thank you both. I am actually home now, and not able to access my test db I am playing with at work. As soon as I get in tomorrow morning, I will try running that simple SELECT to see if anything is returned.
As far as the "Gender" column, I believe it is defined as a varchar(20), but I will double-check to make sure.
Thanks to both of you, and Lowell, for helping a poor ol' web developer out! Ha ha ha! (more poor than old! Lol)
quantum_kev
February 15, 2011 at 5:36 am
Just a thought.
Try changing your
DECLARE @tmpMen TABLE (Name varchar)
and
DECLARE @tmpWomen TABLE (Name varchar)
to
DECLARE @tmpMen TABLE (Name varchar(10))
DECLARE @tmpWomen TABLE (Name varchar(10))
Looks like varchar maybe using the default length of 1 and therefore truncating the output.
Hor_netuk
February 15, 2011 at 5:42 am
CELKO (2/14/2011)
the scoring starts with 100 points.
1) Did the DDL follow ISO standards and have no dialect? +25
2) Did it have no key?? -25
3) Did you use ISO sex codes? +10
4) Did the INSERT statement use dialect? -15
5) Did the query get the pairs wrong? -100
6) Did the query use temp tables instead of subqueries? -50 per temp table
7) Did the query use any procedural code or cursors? -250
8) Did the query use the CROSS JOIN infixed notation? +10
Celko very nice post pointing out the "dos" and "don'ts" when trying to follow some of the ISO standards, thanks.
Lowell
February 15, 2011 at 6:53 am
CELKO (2/14/2011)
CREATE TABLE People(person_id INTEGER NOT NULL PRIMARY KEY,
person_name VARCHAR(35) NOT NULL,
sex_code INTEGER NOT NULL
CHECK(sex_code IN (1, 2)));
INSERT INTO People (person_id, person_name, sex_code)
VALUES (1, 'Fred', 1),
(2, 'Lisa', 2),
(3, 'Pam', 2),
(4, 'Peter', 1);
SELECT guy_name, gal_name
FROM ((SELECT person_name FROM People WHERE sex_code = 1) – guys
CROSS JOIN
(SELECT person_name FROM People WHERE sex_code = 2))-- gals
AS X (guy_name, gal_name)
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '–'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ')'.
I deleted "- guys" and "-- gals" but got:
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'CROSS'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ')'.
February 15, 2011 at 7:25 am
I Think Mr Celkos post was for a db other than SQL...this is the equivalent:
CREATE TABLE People
(person_id INTEGER NOT NULL PRIMARY KEY,
person_name VARCHAR(35) NOT NULL,
sex_code INTEGER NOT NULL
CHECK(sex_code IN (1, 2)));
INSERT INTO People (person_id, person_name, sex_code)
SELECT 1, 'Fred', 1 UNION ALL
SELECT 2, 'Lisa', 2 UNION ALL
SELECT 3, 'Pam', 2 UNION ALL
SELECT 4, 'Peter', 1;
SELECT guy_name, gal_name
FROM (
(SELECT person_name AS guy_name FROM People WHERE sex_code = 1) guys
CROSS JOIN
(SELECT person_name AS gal_name FROM People WHERE sex_code = 2) gals
)
Lowell
February 15, 2011 at 7:56 am
Lowell (2/15/2011)
I Think Mr Celkos post was for a db other than SQL...this is the equivalent:
Thank you.
February 15, 2011 at 8:35 am
You know what, I was wondering about that myself. Thanks for that tip. I will change it to that and see if it works.
Thanks all!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply