Need help with a join

[Danielle]

Hello group-
I am having a problem where joined tables are returning too many rows.
Here is my scenario -

I am trying to create a temporary table from parts of three tables -
the important columns are:

a.id
a.tran_date

b.id
b.geo

c.holiday_date
c.geo

My query is like this

select a.col1, b.col1 from a
inner join b on a.id = b.id
inner join c on c.geo = b.geo
With the parameters that I have, I get 144 rows with just the join of
tables a & b.

However, when I add the join to table c, I get 720 rows - there are (of
course) 5 rows in table c where the geo is the same as the geo in table
b.

The reason for the join is that I need to know if a.tran_date =
c.holiday_date and holidays differ by GEO.

I don't want this added information. Thoughts on what I am doing wrong?

Please let me know if you need more information

Thanks-
Danielle

[David Portas]

Danielle wrote:

Hello group-
I am having a problem where joined tables are returning too many rows.
Here is my scenario -

I am trying to create a temporary table from parts of three tables -
the important columns are:

a.id
a.tran_date

b.id
b.geo

c.holiday_date
c.geo

My query is like this

select a.col1, b.col1 from a
inner join b on a.id = b.id
inner join c on c.geo = b.geo
With the parameters that I have, I get 144 rows with just the join of
tables a & b.

However, when I add the join to table c, I get 720 rows - there are (of
course) 5 rows in table c where the geo is the same as the geo in table
b.

The reason for the join is that I need to know if a.tran_date =
c.holiday_date and holidays differ by GEO.

I don't want this added information. Thoughts on what I am doing wrong?

Please let me know if you need more information

Thanks-
Danielle

Please post DDL and sample data, otherwise any answers you get will
just be guesswork. See:
http://www.aspfaq.com/etiquette.asp?id=5006

My guess is that you can use EXISTS or NOT EXISTS instead of a JOIN to
C. Your spec is too vague for me to be sure though.

--
David Portas
SQL Server MVP
--

[John Bell]

Hi Danielle

I am not sure what you mean by holidays differ by GEO!

Maybe you are wanting to extend the join clause to eliminate rows where the
dates don't match?
e.g.
select a.col1, b.col1
from a
join b on a.id = b.id
join c on c.geo = b.geo AND a.tran_date = c.holiday_date

Otherwise please post DDL, Sample data and expected output as David
reqested.

John
"Danielle" <wx****@aol.com > wrote in message
news:11******** *************@o 13g2000cwo.goog legroups.com...

Hello group-
I am having a problem where joined tables are returning too many rows.
Here is my scenario -

I am trying to create a temporary table from parts of three tables -
the important columns are:

a.id
a.tran_date

b.id
b.geo

c.holiday_date
c.geo

My query is like this

select a.col1, b.col1 from a
inner join b on a.id = b.id
inner join c on c.geo = b.geo
With the parameters that I have, I get 144 rows with just the join of
tables a & b.

However, when I add the join to table c, I get 720 rows - there are (of
course) 5 rows in table c where the geo is the same as the geo in table
b.

The reason for the join is that I need to know if a.tran_date =
c.holiday_date and holidays differ by GEO.

I don't want this added information. Thoughts on what I am doing wrong?

Please let me know if you need more information

Thanks-
Danielle

[manstein]

Hello Danielle

"The reason for the join is that I need to know if a.tran_date =
c.holiday_date and holidays differ by GEO."

Im not sure what you want since this is a bit cryptic but is this code
similar to what you need?

select distinct
a.col1,
b.col1
from a
inner join b on b.id = a.id
inner join c on c.holiday_date= a.tran_date
where b.geo<>c.geo;

Also, you showed some concern about the number of rows returned. Use
"distinct" keyword to get unique records and a more concise number of
rows.

best regards