Using 2 tables want to display results from both

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)

Using 2 tables want to display results from both

Author

Topic

spnz
Starting Member

12 Posts

Posted - 2007-03-30 : 22:31:05

Hi there,
I am having problems with a SQL statement and can't seem to get it to work the way I wish.

I have 2 tables tblItemDetails & tblItemTypes.

A person can have many items and each of those items can be different ItemType e.g. a person may own a tv and dvd that fall into the appliances type, they also may have a fridge and a frezzer that fall into the kitchen wear type.
My current SQL statement returns all the information where I would like for the statement to return only 1 record for appliances and 1 record for kitchen wear.

This is my statement now

SELECT tblItemDetails.ItemTypeID, tblItemDetails.UserID, tblItemDetails.ItemID, tblItemTypes.ItemType
FROM tblItemTypes INNER JOIN
tblItemDetails ON tblItemTypes.ItemTypeID = tblItemDetails.ItemTypeID

Can anyone help out?

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-03-30 : 22:57:15

SELECT top 1 tblItemDetails.ItemTypeID, tblItemDetails.UserID, tblItemDetails.ItemID, tblItemTypes.ItemType
FROM tblItemTypes INNER JOIN
tblItemDetails ON tblItemTypes.ItemTypeID = tblItemDetails.ItemTypeID

TCC

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-03-30 : 22:59:06

select * from @stage
SELECT tblItemDetails.ItemTypeID, tblItemDetails.UserID, tblItemDetails.ItemID, it.ItemType
FROM (select top 1 * tblItemTypes) it INNER JOIN
tblItemDetails ON it.ItemTypeID = tblItemDetails.ItemTypeID

TCC

spnz
Starting Member

12 Posts

Posted - 2007-03-30 : 23:18:59

Hi Tishri,
I have tried both your examples but they didn't work :(

The 1st example runs ok but only returns 1 result where I know there should be 2.

The 2nd example fails giving me an error Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'tblItemTypes'.

What should I change?

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-03-30 : 23:23:47

SELECT tblItemDetails.ItemTypeID, tblItemDetails.UserID, tblItemDetails.ItemID, it.ItemType
FROM (select top 1 * from tblItemTypes) it INNER JOIN
tblItemDetails ON it.ItemTypeID = tblItemDetails.ItemTypeID

TCC

spnz
Starting Member

12 Posts

Posted - 2007-03-30 : 23:30:26

That one runs ok but returns
1 d196a58d-4733-491c-b860-8aabe0711d2f Kitchenware
1 d196a58d-4733-491c-b860-8aabe0711d2f Kitchenware

Where it should return

1 d196a58d-4733-491c-b860-8aabe0711d2f Kitchenware
2 d196a58d-4733-491c-b860-8aabe0711d2f Applicances

Thanks for your help

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-03-30 : 23:35:11

You mean you want to select all ITEMTYPES and return only 1 record for each item type?

TCC

spnz
Starting Member

12 Posts

Posted - 2007-03-30 : 23:41:49

yes sorry if I didnt explain myself very clearly

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-03-30 : 23:44:43

is this what you mean?
you can change the data of the temporary table for make things clear.

declare @itemdetails table (itemtypeid int, userid varchar(200), itemid varchar(100))
declare @itemtypes table (itemtypeid int, itemtype varchar(12))

insert into @itemdetails
select 1, 'd196a58d-4733-491c-b860-8aabe07', '11d2f' union all
select 2, 'd196a58d-4733-491c-b860-8aabe07', '11d2f'

insert into @itemtypes
select 1, 'appliances' union all
select 2 , 'kitchenware'

select * from @itemdetails d
inner join @itemtypes t on d.itemtypeid = t.itemtypeid

TCC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-30 : 23:54:14

spnz,

you better post your table structure with sample data and the result that you want.

spnz
Starting Member

12 Posts

Posted - 2007-03-31 : 00:24:55

Ok the 2 tables

tblItemTypes

ItemTypeID ItemType
1, abc
2, def
3, xyz

tblItemDetails

ItemDetailsID ItemTypeID UserID
1, 1, 1
2, 1, 1
3, 2, 1

I would like the results to look like

UserID ItemTypeID ItemType
1, 1 , abc
1 , 2 , def

Hope that makes sense

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-03-31 : 01:21:57

--run the statement below

declare @tblItemTypes table (ItemTypeID int, ItemType varchar(10))
declare @tblItemDetails table(ItemDetailsID int , ItemTypeID int, UserID int)

insert into @tblItemTypes
select 1, 'abc' union all
select 2, 'def' union all
select 3, 'xyz'

insert into @tblItemDetails
select 1,1,1 union all
select 2,1,1 union all
select 3,2,1

select distinct userid, itemtypeid , (select itemtype from @tblItemTypes where itemtypeid = d.itemtypeid) as itemtype from @tblItemdetails d

TCC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-31 : 02:26:53

"I would like for the statement to return only 1 record for appliances and 1 record for kitchen wear"
Which one would you like to return ?
Here i just return the smallest value of UserID.


select 	UserID = min(d.UserID), t.ItemTypeID , t.ItemType
from 	tblItemDetails d inner join tblItemTypes t
	on	d.ItemTypeID	= t.ItemTypeID
group by t.ItemTypeID , t.ItemType

spnz
Starting Member

12 Posts

Posted - 2007-03-31 : 03:09:47

How would I write this as a stored procedure as I want to be able to select this information based on a users ID.

I would have added @UserID int

AS

....... but UserID has already been added into the select

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-31 : 03:11:24

[code]
select UserID = @UserID, t.ItemTypeID , t.ItemType
from tblItemDetails d inner join tblItemTypes t
on d.ItemTypeID = t.ItemTypeID
where d.UserID = @UserID
group by t.ItemTypeID , t.ItemType
[/code]

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-03-31 : 22:36:17

select distinct userid, itemtypeid , (select itemtype from tblItemTypes where itemtypeid = d.itemtypeid) as itemtype from tblItemdetails d
where userid = @userid

TCC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-01 : 00:34:40

quote:
Originally posted by tishri

select distinct userid, itemtypeid , (select itemtype from tblItemTypes where itemtypeid = d.itemtypeid) as itemtype from tblItemdetails d
where userid = @userid
TCC

Sub-query is not as efficient as INNER JOIN. If the itemtypeid is not the primary key in tblItemTypes table, the sub-query will failed as it will return more than 1 records.

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-04-01 : 21:16:38

ahh,okey thanks for the information

TCC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources