matching columns in table - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

matching columns in table

Author

Topic

aivoryuk
Starting Member

9 Posts

Posted - 2009-12-23 : 09:17:20

Hi all

I would like to perfom a matching query using columns within the same table. the table is below ( a simplified version) with the column headings of fruit and type.

Fruit type
apple granny
apple gala
apple delicious
orange granny
orange satsuma
orange tangerine
banana granny
banana jamaican
banana barbados

what I would like to do is to only return a result if there is a value in the 'type' column that matches all the distinct values in the 'fruit' column. I wouldn't actually know what the value in the 'type' column is.

so it should bring back the value granny.

Is this possible?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-12-23 : 09:18:42

Very possible. First, normalize your database. Then you will see just how easy it is.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

aivoryuk
Starting Member

9 Posts

Posted - 2009-12-23 : 09:57:57

hi

sorry Im not sure I follow

quote:
Originally posted by DonAtWork

Very possible. First, normalize your database. Then you will see just how easy it is.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-23 : 10:13:39

He is asking you to normalize your table...you don't follow that?

If you are using SQL 2005 or above, here is one way of doing it..(not the normalization...but the result that you want..

)

declare @cnt int
select @cnt = count(distinct Fruit) from <urtable>

select [type] from 
(
select row_number() over(partition by [type] order by Fruit) as seq, * from <urtable>
) t
where t.seq = @cnt

This is the sample data I used.

declare @t table 
(Fruit varchar(100),[type] varchar(100))
insert @t
select 'apple', 'granny'
union all select 'apple', 'gala'
union all select 'apple', 'delicious'
union all select 'orange', 'granny'
union all select 'orange', 'satsuma'
union all select 'orange', 'tangerine'
union all select 'banana', 'granny'
union all select 'banana', 'jamaican'
union all select 'banana', 'barbados'

quote:
Originally posted by aivoryuk

hi

sorry Im not sure I follow

quote:
Originally posted by DonAtWork

Very possible. First, normalize your database. Then you will see just how easy it is.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

aivoryuk
Starting Member

9 Posts

Posted - 2009-12-23 : 10:34:01

Hi thanks for that yeah i do follow the normalize the table part.
I should have mentioned that I am attempting this SQL query as part of Microsoft query (excel 2003) but it is something I am looking to do within SQL ( i am full of cold so am probably not thinking straight)

I have tried what you have wrote but it did not work which I would assume due to the fact that I am using microsoft query.

Is there anyway round this/

Many thanks in advance

quote:
Originally posted by vijayisonly

He is asking you to normalize your table...you don't follow that?

If you are using SQL 2005 or above, here is one way of doing it..(not the normalization...but the result that you want..)
declare @cnt int
select @cnt = count(distinct Fruit) from <urtable>

select [type] from
(
select row_number() over(partition by [type] order by Fruit) as seq, * from <urtable>
) t
where t.seq = @cnt

This is the sample data I used.

declare @t table
(Fruit varchar(100),[type] varchar(100))
insert @t
select 'apple', 'granny'
union all select 'apple', 'gala'
union all select 'apple', 'delicious'
union all select 'orange', 'granny'
union all select 'orange', 'satsuma'
union all select 'orange', 'tangerine'
union all select 'banana', 'granny'
union all select 'banana', 'jamaican'
union all select 'banana', 'barbados'

quote:
Originally posted by aivoryuk

hi

sorry Im not sure I follow

quote:
Originally posted by DonAtWork

Very possible. First, normalize your database. Then you will see just how easy it is.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources