473,325 Members | 2,805 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Need help with a join

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

Dec 27 '05 #1
3 1339
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
--

Dec 28 '05 #2
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*********************@o13g2000cwo.googlegro ups.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

Dec 28 '05 #3
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

Dec 28 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me...
9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
3
by: Prem | last post by:
Hi, I am having many problems with inner join. my first problem is : 1) I want to know the precedance while evaluating query with multiple joins. eg. select Employees.FirstName,...
3
by: ColdCanuck | last post by:
Help! I'm trying to understand the new ANSI join syntax (after many years of coding using the old style). I am now working with an application that only understands ANSI syntax so I am...
3
by: google | last post by:
I have a database with four table. In one of the tables, I use about five lookup fields to get populate their dropdown list. I have read that lookup fields are really bad and may cause problems...
2
by: Terry Olsen | last post by:
I need to get information from 3 tables in an MDB file. I need all the columns in the first table. I need 2 columns in the 2nd table where it's primary key matches a column in the first table....
0
by: Chuck36963 | last post by:
Hi all, I've been working on a listing problem and I can't figure out how to work it out. I have looked far and wide on the web to find answers, but I'd like other peoples input on my project in...
2
by: speavey | last post by:
When I run this query, I get an ORDER BY error "Incorrect syntax near the keyword 'ORDER'. I've bolded it below. If I take the ORDER by out then it works correctly, but I need the ORDER BY...
1
by: muld | last post by:
I've inherited a database system designed to calculate sickness absence statistics. There is a main table with an employee ID which also holds the number of days worked by that person in a year ....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.