Microsoft SQL Server forum - Need help with a join

Author

Need help with a join

Danielle

2005-12-27, 8:24 pm

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

2005-12-27, 8:24 pm

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

2005-12-28, 11:24 am

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" <wxbuff@aol.com> wrote in message
news:1135727193.175900.59790@o13g2000cwo.googlegroups.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

2005-12-28, 8:24 pm

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

Sponsored Links