April 9, 2008 at 2:17 pm
I'm hoping someone can help me out -- I am really green when it comes to writing sql queries. 😀
I'm using Microsoft Query (going thru excel) and I'm tying together 3 tables. I'm having trouble with duplicate rows, and I'm wondering if there is a way to show only unique rows. I've tried using DISTINCT, but that doesn't fix it for me.
I do know that 2 of the 3 tables share the same 2 index columns, and the third table only shares 1 of those index columns (the third table has the same column, it's just not an index column). I'm not sure if that makes a difference in what the solution is.
I really appreciate anyone's help. It's a hard SQL world out there for an accountant.
April 9, 2008 at 3:09 pm
Please provide your query. I am pretty sure some people will help you out.
April 9, 2008 at 3:41 pm
SELECT JCCI.Contract AS 'Contract #', JCCI.Description, JCCI.Item, JCCI.Department, JCCI.RetainPCT, JCCI.OrigContractAmt, JCCI.OrigContractUnits, JCCI.OrigUnitPrice, JCCI.ContractAmt, JCCI.ContractUnits, JCCI.UnitPrice, JCCI.BilledAmt, JCCI.BilledUnits, JCCI.ReceivedAmt, JCCI.CurrentRetainAmt, JCCI.BillOriginalUnits, JCCI.BillOriginalAmt, JCCI.StartMonth, JCCM.Description AS 'Job Name', JCCM.ContractStatus, JCCM.StartMonth, JCCM.StartDate, JCCM.MonthClosed, JCCM.CompleteYN, JCJM.udestimator, JCJM.udlettingdate, JCJM.udprojectmanager, JCJM.udtaxexempt, JCJM.udcounty, JCJM.udPRNumberAdj, JCCM.StartDate, JCCM.StartMonth, JCJM.udJurisdictionCd, JCJM.udDept
FROM Viewpoint.dbo.JCCI JCCI, Viewpoint.dbo.JCCM JCCM, Viewpoint.dbo.JCJM JCJM
WHERE JCCI.JCCo = JCCM.JCCo AND JCCM.JCCo = JCJM.JCCo AND JCCI.Contract = JCCM.Contract AND JCCM.Contract = JCJM.Contract AND ((JCCI.JCCo=1) AND (JCJM.TaxGroup=1) AND (JCJM.JCCo=1))
I know this might look jumbled. I couldn't find an article on code pasting etiquette
April 10, 2008 at 11:49 am
What would define a unique row, simply contract? Can you provide table layouts and some sample data? It's not a hard world, just different. Stick around, there's a lot of fun to be had!:D
-- You can't be late until you show up.
April 10, 2008 at 2:43 pm
[font="Arial"]
Hello,
Script would look better like this :
SELECT JCCI.Contract AS 'Contract #',
JCCI.Description,
JCCI.Item,
JCCI.Department,
JCCI.RetainPCT,
JCCI.OrigContractAmt,
JCCI.OrigContractUnits,
JCCI.OrigUnitPrice,
JCCI.ContractAmt,
JCCI.ContractUnits,
JCCI.UnitPrice,
JCCI.BilledAmt,
JCCI.BilledUnits,
JCCI.ReceivedAmt,
JCCI.CurrentRetainAmt,
JCCI.BillOriginalUnits,
JCCI.BillOriginalAmt,
JCCI.StartMonth,
JCCM.Description AS 'Job Name',
JCCM.ContractStatus,
JCCM.StartMonth,
JCCM.StartDate,
JCCM.MonthClosed,
JCCM.CompleteYN,
JCJM.udestimator,
JCJM.udlettingdate,
JCJM.udprojectmanager,
JCJM.udtaxexempt,
JCJM.udcounty,
JCJM.udPRNumberAdj,
JCCM.StartDate,
JCCM.StartMonth,
JCJM.udJurisdictionCd,
JCJM.udDept
FROM Viewpoint.dbo.JCCI JCCI,
Viewpoint.dbo.JCCM JCCM,
Viewpoint.dbo.JCJM JCJM
WHERE JCCI.JCCo = JCCM.JCCo
AND JCCM.JCCo = JCJM.JCCo
AND JCCI.Contract = JCCM.Contract
AND JCCM.Contract = JCJM.Contract
AND ( (JCCI.JCCo=1)
AND (JCJM.TaxGroup=1)
AND (JCJM.JCCo=1) )
We could help more if we knew that the JCCo was a key in JCCI and that the JCCo field was a foreign key in the other two tables ( JCCM and JCJM ). In your where clause you are tying the tables together with the Contract column and the JCCo columns. This is bound to be a prolem that may be the cause of the duplicate records. Can you tell us if JCCo is an integer identity column in table JCCI and that the JCCo integer is a column in the other two tables?
Regards,
Terry
[/font]
April 10, 2008 at 3:29 pm
Thanks for everyone's replies. 🙂
Here is what I know - all 3 tables have JCCo as an indexed column. Tables JCCI and JCCM both have Contract as an indexed column, while in JCJM it is just a normal varchar column.
I would post a picture of what the result set is, but photobucket is blocked on our company network. Basically I can have multiple records of JCCI.Contract (for example contract # 2000), and each contract can have multiple sub parts (for example, JCCI.Description have more than 1 description depending on the type of work) but I should only have 1 of each type of JCCI.Description that is on each contract. Right now I am getting multiples of JCCI.Description and JCCI.BilledAmt just to name a couple.
April 11, 2008 at 7:14 am
[font="Arial"]Hello and Good Morning,
In table JCCI, is JCCo an int and an identity field?
In table JCCI, is Contract a unique value?
In table JCCM is JCCo the value from table JCCI's JCCo field?
From just looking at the field names in the tree tables, it looks like table JCCI is the driver table. Then JCCM is a one to many relationship to JCCI. Then JCJM is a one to many relationship to JCCM. Is this true?
If the relationships are as suggested then the where clause needs to be some thing like :
where JCCI.Contract = JCCM.Contract
and JCCM.Contract = JCJM.Contract
and ( JCCI.JCCo = 1
and JCJM.TaxGroup = 1
and JCJM.JCCo = 1)
The above 'assumes' that JCCo is just a int identity field with a common name in all three tables and not a foreign key.
This guessing of the where clause on my part is due to know knowing the table relationships for sure. Since you have confirmed that Contract is the common link between the three tables, the above guess should work. If however, JCCo is a foreign key then the use of Contract in the where clause will probably not be needed.
Can you disclose the type and data relationship of the JCCo and Contract fields in each table?
Regards,
Terry
[/font]
April 11, 2008 at 11:16 am
I think I am following what you are asking. I don't know how to determine if a column is a primary or foreign key. :unsure: My experience with databases in general is very limited
I am going to go out onto a limb here and say that JCCo is a foreign key on all 3 tables, and when I use a where clause linking only by that column I don't get duplicate records from tables JCCI and JCCM, but the columns I pull from JCJM don't seem to match up properly.
I am also going to go out on a limb and say that "Contract" is a foreign key on JCCI and JCCM but not on JCJM.
When I link all 3 tables with "Contract" then I get multiple records for everything.
If I understand this correctly, if I know what the primary and foreign keys are in the tables, then those are the columns I want to use when I link them together.
April 11, 2008 at 1:42 pm
[font="Arial"]Hello,
To see the table definitions you can use SQL Analyzer or the freeware version of TOAD. Either of these tools will show you what the keys are, if they are unique, and the constraints ( foreign key ) relation ships.
If you don't have either of the above two tools, usually the report tool you are using will have some way of letting you know. Perhaps export the the data to Access if you have that and use it to look at the relationships.
Have a great week end.
Regards,
Terry
[/font]
April 12, 2008 at 11:15 am
Have you tried to use the JOIN statement instead of WHERE clause?
And then, group the similar items together by using a Group By function. Maybe it will work then.:hehe:
April 14, 2008 at 10:50 am
I downloaded TOAD - this looks like it's going to a pretty handy tool, thanks for the tip.
There are no key's on any of the three tables, and I don't see anything under the "Foreign Keys" tab or the "Check Constraints" tab. So is this going to be a problem when I try to link tables together?
I thought about using join clauses, but since I am going through Microsoft Query, and since it does everything for you on the graphical interface, it automatically uses the where clause. If I was more sophisticated in my query writing skills I would go that route. I will have to give that a try.
Thanks for everyone's help so far.
April 14, 2008 at 12:26 pm
[font="Arial"]Hello,
There shouldn't be a problem due to their not being foreign keys on the tables. The where clause is how SQL joined tables together before Microsoft promoted the use of the JOIN methodology. The issue is performance but unless we're talking many hundred thousand records it won't matter to you.
I would suggest you start with the primary table and get a where clause in the query tool you are using to link those two tables together to get a unique row. Then try adding the third table with a link to the second and not the first.
I would also suggest that you don't link both the integer and the Contract in the where clause. Try just the integer first.
That may get what you want. If not use just the Contract numbers to link the three tables.
Let me know how it goes. When you look at the tables in TOAD you should be seeing a the table definitions as to integer, float, varchar etc. It would be good to know this information so I can help you more specifically.
Regards,
Terry
[/font]
April 14, 2008 at 2:21 pm
Hi Terry,
I've tried using table JCCI as the primary table, and linking the other 2 off just JCCo and just Contract and I still come up with the same result. I think what is happening is the columns I am pulling from table JCJM are causing the duplicates. Most of the data I need is in tables JCCI and JCCM, I really only need 1 column of data from JCJM, but I was trying to be proactive because I know at some point my boss is going to want to see more information from that table linked with the other 2.
The common columns between all 3 tables are:
JCCo, tinyint, key is not checked, not null is not checked, and both default and rule are null
&
Contract, varchar(10), key is not checked, not null is checked, and both default and rule are null
Those are the only 2 to link the tables with. This is a common problem I have when I try and link more than 2 tables together. I'm not sure if I'm just missing something or if it's the way the database has been designed.
Thanks for you guidance,
Kelly
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply