MS SQL Server - Question on results from Table Joins.

Author

Question on results from Table Joins.

ronnie.walker@celticgems.co.uk

2005-08-12, 11:23 am

Hi there - i am relatively new to SQL server so please bear with me. I
am attempting to join several tables so that I can generate an asset
report. The query I am runnins is

-- Start of Main Select Query
select t_humanresources.fname as 'First Name',
t_humanresources.lname as 'Last Name',
t_humanresources.hridentifier1 as 'HR Identifier',
t_humanresources.locationid1 as 'Location'
--t_catalogs.model as 'Catalog Item Name',
--t_catalogs. manufacturerproductc
ode

from T_Catalogs
inner join t_assets
on t_catalogs.t_catalog_id = t_assets.t_catalog_id

inner join t_Teamresponsible_LU
T
on t_assets. t_teamresponsible_id
=
t_teamresponsible_lu
t. t_teamresponsible_id

inner join t_internallocations
on t_assets. t_internallocation_i
d =
t_internallocations. t_internallocation_i
d

inner join t_activedescriptions
_LUT
on t_assets. t_activedescription_
id =
t_activedescriptions
_LUT. t_activedescription_
id

inner join t_category_lut
on t_assets.t_category_id = t_category_lut.t_category_id

inner join t_assetusers
on t_assets.t_asset_id = t_assetusers.t_asset_id

inner join t_humanresources
on t_assetusers.t_hr_id = t_humanresources.t_hr_id

where t_humanresources.Lname = 'Capp'

My problem is when I run the above query I am getting multiple returns
against the particular surname even though there is only 1 person with
the surname in the particular t_humanresources table. The same is
happening for other fields i have listed as well (although I have
commented these out above).

Does anyone have any ideas as to why this is happening as all I want to
see is a list of Users and the particular hardware assigned to them.

Thanks
Ronnie

Adam Machanic

2005-08-12, 11:23 am

<ronnie.walker@celticgems.co.uk> wrote in message
news:1123857858.974507.278050@z14g2000cwz.googlegroups.com...
>
> Does anyone have any ideas as to why this is happening as all I want to
> see is a list of Users and the particular hardware assigned to them.

Can't users have more than one piece of hardware assigned to them?

--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--

Perayu

2005-08-12, 8:23 pm

Try to use Left Join instead of Inner Join.

Perayu

"ronnie.walker@celticgems.co.uk" wrote:

> Hi there - i am relatively new to SQL server so please bear with me. I
> am attempting to join several tables so that I can generate an asset
> report. The query I am runnins is
>
> -- Start of Main Select Query
> select t_humanresources.fname as 'First Name',
> t_humanresources.lname as 'Last Name',
> t_humanresources.hridentifier1 as 'HR Identifier',
> t_humanresources.locationid1 as 'Location'
> --t_catalogs.model as 'Catalog Item Name',
> --t_catalogs. manufacturerproductc
ode
>
> from T_Catalogs
> inner join t_assets
> on t_catalogs.t_catalog_id = t_assets.t_catalog_id
>
> inner join t_Teamresponsible_LU
T
> on t_assets. t_teamresponsible_id
=
> t_teamresponsible_lu
t. t_teamresponsible_id

>
> inner join t_internallocations
> on t_assets. t_internallocation_i
d =
> t_internallocations. t_internallocation_i
d
>
> inner join t_activedescriptions
_LUT
> on t_assets. t_activedescription_
id =
> t_activedescriptions
_LUT. t_activedescription_
id
>
> inner join t_category_lut
> on t_assets.t_category_id = t_category_lut.t_category_id
>
> inner join t_assetusers
> on t_assets.t_asset_id = t_assetusers.t_asset_id
>
> inner join t_humanresources
> on t_assetusers.t_hr_id = t_humanresources.t_hr_id
>
> where t_humanresources.Lname = 'Capp'
>
>
> My problem is when I run the above query I am getting multiple returns
> against the particular surname even though there is only 1 person with
> the surname in the particular t_humanresources table. The same is
> happening for other fields i have listed as well (although I have
> commented these out above).
>
> Does anyone have any ideas as to why this is happening as all I want to
> see is a list of Users and the particular hardware assigned to them.
>
> Thanks
> Ronnie
>
>

Hugo Kornelis

2005-08-12, 8:23 pm

On 12 Aug 2005 07:44:19 -0700, ronnie.walker@celticgems.co.uk wrote:

>Hi there - i am relatively new to SQL server so please bear with me. I
>am attempting to join several tables so that I can generate an asset
>report. The query I am runnins is
(snip)

Hi Ronnie,

In this query, you are joining to several tables that are not used. If
one row from your data matches several rows in those unused tables, the
row will appear to be duplicated - each occurence of the "duplicated"
row is linked to another row in the other table, but you can't see that
because those values are not displayed.

Try what happens if you remove the unused tables:

-- Start of Main Select Query
select t_humanresources.fname as 'First Name',
t_humanresources.lname as 'Last Name',
t_humanresources.hridentifier1 as 'HR Identifier',
t_humanresources.locationid1 as 'Location',
t_catalogs.model as 'Catalog Item Name',
t_catalogs. manufacturerproductc
ode

from T_Catalogs
inner join t_assets
on t_catalogs.t_catalog_id = t_assets.t_catalog_id

inner join t_assetusers
on t_assets.t_asset_id = t_assetusers.t_asset_id

inner join t_humanresources
on t_assetusers.t_hr_id = t_humanresources.t_hr_id

where t_humanresources.Lname = 'Capp'

And if you comment the two columns you take from t_catalogs, then you
should remove that table from the FROM clause as well.

If the above doesn't help, then see www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Hugo Kornelis

2005-08-12, 8:23 pm

On Fri, 12 Aug 2005 11:25:57 -0700, Perayu wrote:

>Try to use Left Join instead of Inner Join.
>
>Perayu

Hi Perayu,

How would a left join help to remove unwanted duplicates from the
results of the query?

I do see how it might return even more unwanted rows. And how it would
lower performance. But removing duplicates?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links