Solved SQL to append new columns to table (1 Viewer)

[jaryszek]

Hi Guys,

i have 2 tables:
Table1

ID	Pet	Kind	Color
1​	Cat	Mammal	Black
2​	Dog	Mammal	White

and

Table2 Table2

ID	Food
1​	Fish
2​	Meet

What i want to do is to get result like here:
ExpectedResult

ID	Pet	Kind	Color	Food
1​	Cat	Mammal	Black	Fish
4​	Cat	Mammal	Black	Meet
5​	Dog	Mammal	White	Fish
6​	Dog	Mammal	White	Meet

so as you can see for Eech Per and Kind column (co Mammal and Dog and Cat) i just appended one new column = Food.

How can i write SQL to do it?
Best,
Jacek

 

[Uncle Gizmo]

You don't need cat ID 1 & cat ID 4, or Dog ID 5 & Dog ID 6.

You only need one ID for each animal:-

Cat ID 1, Dog ID 5



You need an extra table:-

PetAndFoodJoin

Pet ID --- FoodID
----1------------ 1
----1------------ 2
--- 5 ----------- 1
--- 5 ----------- 2

From this arrangement you can get the results you desire ..

 

[jaryszek]

Hmm and it is not possible to do this without additional tables only with SQL?

Thanks!

Best,
Jacek

 

[Uncle Gizmo]

Hmm and it is not possible to do this without additional tables only with SQL?

I'm sure that it is possible. There are some very clever people here, who will write code that can do just about anything.

But if you go with the wisdom of the experienced database developers on this site, you will find that the approach I advise has many advantages..

Anything else you do is likely to become a nightmare very quickly!

 

[jaryszek]

Thank you,

let's wait i want to know it And will decide.

Best,
Jacek

 

[June7]

A Cartesian query can produce the output you show (except for the ID field). What purpose do you want this for?

SELECT Table1.*, Table2.Food FROM Table1, Table2;


BTW, correct spelling: "meat", not "meet".

 

[jaryszek]

Thank you Guys,

i needed cross join here,

Best,
Jacek

 

[Pat Hartman]

I can't make sense of the tables or the question. Cats and dogs are both carnivores and so would eat similar foods But what if you had other animal types? Birds and Fish don't eat the same foods as cats and dogs and neither to cows.