SELECT HELP - 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

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT HELP

Author

Topic

aug
Starting Member

4 Posts

Posted - 2007-11-13 : 23:08:03

I have the following tables

| NameId | Name |
| 1 | aug |
| 2 | other |

| TypeId | Name |
| 1 | Home |
| 2 | Car |

| NameId | TypeId | Value |
| 1 | 1 | Blue |
| 1 | 2 | Black |
| 2 | 1 | Red |
| 2 | 2 | Black |

First, is there anyway to get a row like this:
|Name | Home | Car |
|aug | Blue | Black |

But my real question is this

I am trying to say
SELECT [NameId] FROM Table3 WHERE
(
([TypeId] = 1 AND [Value] = 'Blue') AND
([TypeId] = 2 AND [Value] = 'Black')
)

I can say

SELECT [NameId] FROM Table3 WHERE
(
([TypeId] = 1 AND [Value] = 'Blue') OR
([TypeId] = 2 AND [Value] = 'Black')
)
GROUP BY [NameId]

But that would obviously returns both rows

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-13 : 23:44:55

First,

You can look into PIVOT function for that kind of operation, but not with that data.

Homework?

Poor planning on your part does not constitute an emergency on my part.

aug
Starting Member

4 Posts

Posted - 2007-11-14 : 00:07:11

No this isn't homework I am just trying to figure something out. I don't believe you can Pivot a table like that, and correct me if I am wrong but you have to hardcode in the headers. Thanks for the help though.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 00:17:41

You can pivot like that, you don't have to hardcode in the headers. The point of a pivot is to spin values in rows to column headers. But what you propose in it's exact format...well check these
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6216
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

Poor planning on your part does not constitute an emergency on my part.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-14 : 00:21:44

I always preffer changing the display of the data on the front end (i.e. Pivot table), but if you must do it in sql, then do look into the pivot table.

For the second question, I am not sure if I am following you 100% If you want the unique name id's that meet the where clause just do this


SELECT distinct a.NameId 
FROM Table3 a 
WHERE 
(a.TypeId = 1 AND a.[Value] = 'Blue') OR
(a.TypeId = 2 AND a.[Value] = 'Black')

If this isn't what you're looking for, please explain further.

aug
Starting Member

4 Posts

Posted - 2007-11-14 : 00:25:07

I must not have explained this well. I need to select the ID that has a Blue Car and a Black House, not the IDs that have a Blue Car or a Black House.
So your query would select BOTH the rows in the table.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 00:50:24

Hmmm. Might be tought to do. Neither of those combos have a blue car AND a black house.



Create Table #T1 ( NameId int not null, Name char(5) not null)
Insert Into #T1 (NameID, Name)
Select 1, 'aug' UNION ALL
Select 2, 'other'

Create Table #T2 (TypeID int not null, Name char(5) not null)
Insert Into #T2(TypeID, Name)
Select  1 , 'Home' UNION ALL
Select  2 , 'Car ' 
Create Table #T3 (NameID int not null,TypeID int not null,Value char(5) not null)
Insert Into #T3
Select 1 , 1 , 'Blue' UNION ALL
Select 1 , 2 , 'Black' UNION ALL
Select 2 , 1 , 'Red' UNION ALL
Select 2 , 2 , 'Black'


Select #T1.Name,#T3.Value as COLOR,(Case When #T2.TypeID = 2 then 'CAR' Else 'HOME' end) as Type

From #T1 inner join #T3 
		on #T1.NameID = #T3.NameID
	 inner join #T2 
		on #T3.TypeID = #T2.TypeID
-- Where 
-- (#T2.TypeID = 1 and #T3.Value = 'Blue') AND
--    (#T2.TypeID = 2 and #T3.Value = 'Black')
ORDER by #T1.Name

Drop Table #T1
Drop Table #T2
Drop Table #T3

Play with the filters on that and see if you can find what you need.

Poor planning on your part does not constitute an emergency on my part.

aug
Starting Member

4 Posts

Posted - 2007-11-14 : 01:08:58

I have never really had cause to use the exists command, but I did try it and I am always receiving all of the rows.

For instance I ran the following statements:


CREATE TABLE #test
(
    [NameId] tinyint NOT NULL,
	[TypeId] tinyint NOT NULL,
    [Value]  varchar(50) NOT NULL
)

INSERT INTO #test VALUES (1,1, 'Blue');
INSERT INTO #test VALUES (1,2, 'Black');
INSERT INTO #test VALUES (2,1, 'Red');
INSERT INTO #test VALUES (2,2, 'Black');  
INSERT INTO #test VALUES (3,1, 'White');
INSERT INTO #test VALUES (3,2, 'White');
INSERT INTO #test VALUES (4,1, 'White');
INSERT INTO #test VALUES (5,2, 'White');  
INSERT INTO #test VALUES (6,1, 'White');
INSERT INTO #test VALUES (6,2, 'White');
INSERT INTO #test VALUES (7,1, 'White');
INSERT INTO #test VALUES (7,2, 'White');  

SELECT DISTINCT a.[NameId ]
FROM #test a 
WHERE 
EXISTS (SELECT * FROM #test b WHERE (b.[TypeId] = 1 AND b.[Value] = 'Blue')) AND
EXISTS (SELECT * FROM #test c WHERE (c.[TypeId] = 2 AND c.[Value] = 'Black'));


drop table #test

I got the following results
1
2
3
4
5
6
7

I do appreciate the help though

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-14 : 01:20:45

Wow it's late, and my mind is mush.


CREATE TABLE #test
(
    [NameId] tinyint NOT NULL,
	[TypeId] tinyint NOT NULL,
    [Value]  varchar(50) NOT NULL
)

INSERT INTO #test VALUES (1,1, 'Blue');
INSERT INTO #test VALUES (1,2, 'Black');
INSERT INTO #test VALUES (2,1, 'Red');
INSERT INTO #test VALUES (2,2, 'Black');  
INSERT INTO #test VALUES (3,1, 'White');
INSERT INTO #test VALUES (3,2, 'White');
INSERT INTO #test VALUES (4,1, 'White');
INSERT INTO #test VALUES (5,2, 'White');  
INSERT INTO #test VALUES (6,1, 'White');
INSERT INTO #test VALUES (6,2, 'White');
INSERT INTO #test VALUES (7,1, 'White');
INSERT INTO #test VALUES (7,2, 'White');  

SELECT DISTINCT a.[NameId]
FROM #test a 
WHERE 
	(a.[TypeId] = 1 AND a.[Value] = 'Blue')
and exists (select * from #Test b where a.NameID = b.nameID and b.[TypeId] = 2 AND b.[Value] = 'Black') 
	

drop table #test

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-14 : 01:23:37

here's another example. Check your plan execution for which works better for you


Select * from #Test a
Inner Join #Test b
on a.NameID = b.NameID
where 
a.[TypeId] = 1 AND a.[Value] = 'Blue'
and
b.[TypeId] = 2 AND b.[Value] = 'Black'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources