Selecting Distinct From Multiple Tables

Question

Post reply

Selecting Distinct From Multiple Tables

Hrhb.mail

Old Hand

Points: 334
More actions
June 4, 2014 at 12:48 pm

#292027

I have 3 tables:
News:
bigint NewId
nvarchar NewTitle
datetime NewDate
nvarchar NewBrief
--------------------------
Category:
int CatId
nvarchar CatName
--------------------------
NewsRelCategory:
bigint Id
int CategoryIdFk
bigint NewsIdFk
--------------------------
I want to select NewId, NewDate and Distinct NewTitle
I tried this but NewTitle doesn't distinct:
SELECT
FROM dbo.Category INNER JOIN
NewsRelCategory ON dbo.Category.CatId = NewsRelCategory.NrcCategoryIdFk INNER JOIN
dbo.News ON NewsRelCategory.NrcNewsIdFk = dbo.News.NewId
Help:-)

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Why are you joining all three tables if you only use tables from News?

Could you post sample data and expected results? preferably in the form of INSERT statements.

Here's how you're supposed to post DDL:

CREATE TABLE News(

NewId bigint ,

NewTitle nvarchar(100),

NewDate datetime,

NewBrief nvarchar(100))

--------------------------

CREATE TABLE Category(

CatId int,

CatName nvarchar(100))

--------------------------

CREATE TABLE NewsRelCategory(

Id bigint,

CategoryIdFk int ,

NewsIdFk bigint)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Hrhb.mail Old Hand Points: 334 More actions · Answer 2

Why are you joining all three tables if you only use tables from News?

Because I need values of other tableas, too.

Could you post sample data and expected results?

NewIdNewTitleNewDate

1A2014-06-04 12:11:17.087

2B2014-06-04 12:11:17.087

3C2014-06-04 12:11:17.087

4D2014-06-04 12:11:17.087

5A2014-06-04 12:11:17.087

6A2014-06-04 12:11:17.087

7A2014-06-04 12:11:17.087

8A2014-06-04 12:11:17.087

9B2014-06-04 12:11:17.087

CatIdCatName

1C1

2C2

3C3

4C4

5C5

NrcIdNrcNewsIdFkNrcCategoryIdFk

111

221

331

412

514

635

734

841

955

Result

NewIdNewTitleNewDateNrcCategoryIdFk

1A2014-06-04 12:11:17.0871

2B2014-06-04 12:11:17.0871

3C2014-06-04 12:11:17.0871

4D2014-06-04 12:11:17.0871

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

Can you post your sample data as inserts? That let's us work on the issue instead of turning your data into something we can use.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Hrhb.mail Old Hand Points: 334 More actions · Answer 4

Insert Into News (NewTitle, NewDate) Values ('A','2014-06-04 12:11:17.087')

Insert Into News (NewTitle, NewDate) Values ('B','2014-06-04 12:11:17.087')

Insert Into News (NewTitle, NewDate) Values ('C','2014-06-04 12:11:17.087')

Insert Into News (NewTitle, NewDate) Values ('D','2014-06-04 12:11:17.087')

Insert Into News (NewTitle, NewDate) Values ('A','2014-06-04 12:11:17.087')

Insert Into News (NewTitle, NewDate) Values ('B','2014-06-04 12:11:17.087')

Insert Into Category (CatName) Values ('C1')

Insert Into Category (CatName) Values ('C2')

Insert Into Category (CatName) Values ('C3')

Insert Into Category (CatName) Values ('C4')

Insert Into Category (CatName) Values ('C5')

Insert Into NewsRelCategory (NrcNewsIdFk, NrcCategoryIdFk) Values (1,1)

Insert Into NewsRelCategory (NrcNewsIdFk, NrcCategoryIdFk) Values (2,1)

Insert Into NewsRelCategory (NrcNewsIdFk, NrcCategoryIdFk) Values (3,1)

Insert Into NewsRelCategory (NrcNewsIdFk, NrcCategoryIdFk) Values (1,2)

Insert Into NewsRelCategory (NrcNewsIdFk, NrcCategoryIdFk) Values (1,4)

Insert Into NewsRelCategory (NrcNewsIdFk, NrcCategoryIdFk) Values (3,5)

Insert Into NewsRelCategory (NrcNewsIdFk, NrcCategoryIdFk) Values (3,4)

Insert Into NewsRelCategory (NrcNewsIdFk, NrcCategoryIdFk) Values (4,1)

Insert Into NewsRelCategory (NrcNewsIdFk, NrcCategoryIdFk) Values (5,5)

and RESULT that I need showed in my second post

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

Using the ddl that Luis posted and changing a couple column names there are lots of NULLs in this data. I am willing and able to help. I just need tables with data that I can use to write queries. I just don't have the time to piece all this together into something usable.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Can you explain how do you get to that result?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Hrhb.mail Old Hand Points: 334 More actions · Answer 7

Can you explain how do you get to that result?

This is my question! How can I get to that result?

Sean Lange SSC Guru Points: 286609 More actions · Answer 8

Hrhb.mail (6/4/2014)
Can you explain how do you get to that result?
This is my question! How can I get to that result?

He means what are the business rules to get that? For example, you don't have NewId 1 in your output. Why not? We can help with the query portion but we have to know what the rules are.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

Here's the whole code with no errors.

What rules do we follow to get to your expected results?

CREATE TABLE News(

NewId bigint identity ,

NewTitle nvarchar(100),

NewDate datetime,

NewBrief nvarchar(100))

--------------------------

CREATE TABLE Category(

CatId int identity,

CatName nvarchar(100))

--------------------------

CREATE TABLE NewsRelCategory(

Id bigint identity,

NrcCategoryIdFk int ,

NrcNewsIdFk bigint)