Select Distinct and Inner Join

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 2000 Forums

Transact-SQL (2000)

Select Distinct and Inner Join

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-16 : 11:00:16

Having a little trouble with the syntax here.

I have a table:


CREATE TABLE [dbo].[tblProducts] (

	id          int          IDENTITY(1,1) CONSTRAINT tblProducts_PK PRIMARY KEY NOT NULL ,
	vwr				INT		NOT NULL,
	revision		VARCHAR(10)		NOT NULL,
	reqType			VARCHAR(50),
	leg			VARCHAR(3),
	img1			IMAGE		
) ON [PRIMARY]

I need 3 fields: id, vwr, and reqType

But, I need the dataset to be distinct by unique vwr's.

I've tried something like this but I'm getting a syntax error:


select	t.id, t.vwr, t.reqType
(
	select distinct vwr from tblVWR
) as d 
inner join tblVWR t
on	d.vwr	= t.vwr

I'm not a tsql guru and only deal with the db on occasion.
I've searched around for a solution but just can't seem to get the syntax down.

Suggestions?

Thanks,

Zath

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-16 : 11:02:27

select distinct vwr from tblVWR
inner join tblVWR t
on d.vwr = t.vwr

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-16 : 11:03:11

[code]

select t.id, t.vwr, t.reqType
from
(
select distinct vwr from tblVWR
) as d
inner join tblVWR t
on d.vwr = t.vwr
[/code]

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-16 : 11:04:15

THANKS!

But with a little adjustment, I got it working:

select distinct d.vwr from tblVWR AS d
inner join tblVWR t
on d.vwr = t.vwr

Zath

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-16 : 11:11:21

Ok, I take that back. As soon as I added the other fields, I got duplicates again.

select t.id, t.vwr, t.reqType
from
(
select distinct vwr from tblVWR
) as d
inner join tblVWR t
on d.vwr = t.vwr

AND this one:

select distinct d.id, d.vwr, d.reqType from tblVWR AS d
inner join tblVWR t
on d.vwr = t.vwr

Are both giving me duplicate vwr's (the 1000):

1000 MEC 7
1000 MEC 8
1018 TEST 6
1024 EE 1
1234 NULL 3
3333 NULL 4
5555 NULL 2
12345 NULL 5
99999 NULL 9

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-16 : 11:18:30

quote:

1000 MEC 7
1000 MEC 8

so which record do you want ?

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-16 : 11:22:11

Either one is fine. Doesn't matter.

An easy fix would be to leave the id out, but I need that as well.
Especially since, for instance, 1000 will ALWAYS have the MEC.

Zath

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-16 : 11:28:55

You have to understand..they are not duplicates...

USe MIN() or MAX() on the last cloumn and use GROUP BY

So now let me ask, if it doesn't matter, what value is the third column to you...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-16 : 11:32:17

The third column, the id, is the primary key and I use that for another reason in the gridview.

MIN or MAX?

Never used those before. Have a link for a sample?

Zath

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-11-16 : 12:02:21

F1 is your best friend. Pull up Query Analyzer. Push F1. Tadah! The help file, which will explain MIN and MAX quite well.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-16 : 12:56:04

quote:
Originally posted by Zath

The third column, the id, is the primary key and I use that for another reason in the gridview.

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@
@
@
@
@
@
@
@
@
@
@
@
@
@
@
@
@

That's a red glag btw

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-11-16 : 16:45:32

Min/Max as Brett suggested:
SELECT min(id), vwr, min(reqType)
FROM tblVWR
GROUP BY vwr

but is the data meaningful? in your example, you had a reqtype of both 7 & 8 for vwr= 'MEC' but do don't care which shows up?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources