CODEWITHSUNDEEP
mysqlsqljoin
TDK
TDK

Reputation: 23

how to join 2 tables having equal attributes but different data in each table using mysql

Table 1

create table itemType1(
  maincatg varchar(25), 
  subcatg varchar(25), 
  price float(5), 
  primary key(maincatg, subcatg) 
);

Table2

create table itemType2(
     maincatg varchar(25), 
     subcatg varchar(25), 
     price float(5), 
     primary key(maincatg, subcatg) 
);

Upvotes: 1

Views: 415

Answers (2)

Syntax Error
Syntax Error

Reputation: 4527

This kind of table is going to be much easier to work with.

create table item(
  maincatg varchar(25),
  subcatg varchar(25), 
  price float(5),
  type tinyint(1),
  primary key(maincatg, subcatg) 
);

It also looks like you're going to have a lot of duplicate data in your maincatg and subcatg columns. If I was doing it I would have those in another table or possibly two. You join them with ids.

You will probably have an easier time if you read up on database normalization. It's an easy concept to brush up on as long as you find a decent tutorial and there are plenty out there.

If you're stuck using a database that you can't change for whatever reason, you should probably edit your question to say so, or else most answers will probably be similar to mine.

Upvotes: 0

Matthew Haugen
Matthew Haugen

Reputation: 13286

It sounds like you're looking for a UNION rather than a JOIN.

(SELECT maincatg, subcatg, price
FROM itemType1)

UNION ALL

(SELECT maincatg, subcatg, price
FROM itemType2)

This is effectively a concatenation of the two tables.

Beyond that, I'm really not sure what you're hoping to accomplish.

Upvotes: 2

Related Questions