Problem with ADO quering tables related with primary+foreign key

Question

0.00/5 (No votes)

See more:

Hello,everyone!

I have worked on a project which involves dynamic SQL usage, and there came out an issue.

Simple queries,joins,alter and stuff work well,but this is the issue:

Let us say I have 2 tables,related via primary+foreign key.
Let me dim them as table A and table B.

Once I type SQL query "select data_from_A,data_from_B from A,B;" I get cartesian product of data from both tables.

To clarify even more:

Lets say table A holds names,and table B their salaries.
Once the above query is started I should get the following:

Alex | 1000
Max | 2000
.
.
.

yet I get:

Alex | 1000
Alex | 2000
Max | 1000
Max | 2000
.
.
.

and so on.

Any reference/code example is highly appreciated.

I worked in C++, and have used ADO.

EDIT:
After discussion with professor,we have determined that he made mistake,since he didn't pay attention carefully when I was typing.
Thus,the solution suggested by nv3 is the only possible way.
Therefore I close this thread as answered.
THANKS AGAIN nv3!

Posted 28-Jun-12 21:38pm

MyOldAccount

Updated 29-Jun-12 1:35am

v3

Add a Solution

Comments

MyOldAccount 29-Jun-12 5:07am

UPDATE:

I have created the same query in MS ACCESS(the project uses MS ACCESS database) and the result is the same.

I get cartesian product.

I think that the result is correct and that my professor has made the mistake.

After typing "select name,salary from A,B;" the logical result should be cartesian product.

Can someone confirm that the only way to avoid cartesian product is with WHERE-clause insertion, described by nv3 bellow?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

nv3 · Accepted Answer · 2012-06-28T22:25:00

Solution 1

You should put a where-clause in your SQL statement, like

... WHERE B.name = A.name ...

to select only those tuples from the cartesian product that match regarding the key that links the tables logically.

Posted 28-Jun-12 22:25pm

nv3

Comments

MyOldAccount 29-Jun-12 4:48am

Thank you.

I did that,and then it works fine.
That was the way I thought it should be done.

However my professor said that it shouldn't require WHERE-clause since the tables are linked.

Is it the only way?

I would like to get that result simply by typing something like:

"select name,salary from A,B;"

instead
"select name,salary from A,B where A.name = B.name;"
since the tables are linked with primary/foreign key.

Is it even possible?
Again,thanks man for reply!

member60 29-Jun-12 5:01am

+5

nv3 29-Jun-12 5:05am

Thanks!