How do I join 2 tables with different number of columns and no common column with duplicating?

Question

0.00/5 (No votes)

See more:

I have a table of 2 columns for my Customer table and 3 columns for my Item table. They have no common columns. How do I join them where there's a condition set to it too? i need to implement a table of all the customer details whose price > 200 but i keep getting duplicates :(

something like this

Customer_id Customer_Name price
1002 Amp 250
1166 Boo 1000

What I have tried:

SELECT cust_id, cust_name, price FROM Customer FULL JOIN Item WHERE Item.price > 200

Posted 30-May-20 23:14pm

Member 14848853

Updated 30-May-20 23:22pm

v2

Add a Solution

Comments

[no name] 31-May-20 5:34am

I assume the "three field table" you show in the question is the "customer_item" table.
Now show also the two fields of your customer table.

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)

phil.o · Answer 1 · 2020-05-30T23:23:00

You cannot join two tables if they are not related to each other.
You should have a table between Customer and Item tables, which would for example record the items bought by your customers. For now, both tables are completely unrelated, so there is no way to join them in a sensible way.
Moreover, what does mean the price of a customer? Do you mean the total sum of bought items prices by that customer? In that case, you definitely need a table between Cutomer and Item. This is called a many-to-many relationship, and you cannot model it with only two tables.