CODEWITHSUNDEEP
mysqljoin
soredive
soredive

Reputation: 813

mysql join problem

table a
-------------------------------------------------
id   name     wishlist1    wishlist2     wishlist3

1    john      1              2            3
2    paul      4              5


table b
--------------------------------------------------
id    goods

1     car
2     aircraft
3     bicycle
4     motorbike
5     ipad


result i want to get
---------------------------------------------------
john    car        aircraft    bicycle
paul    motorbike  ipad

how could i get this result? (in mysql)

Upvotes: 5

Views: 77

Answers (4)

Marcelo Cantos
Marcelo Cantos

Reputation: 186108

That's usually a bad idea. I suggest structuring your tables as follows:

person
pid name
--- ----
 1  john
 2  paul

good
gid item
--- ---------
 1  car
 2  aircraft
 3  bicycle
 4  motorbike
 5  ipad

person_wants_good
pid gid
--- ---
 1   1
 1   2
 1   3
 2   4
 2   5

Then, to get the results you want, use the following query:

SELECT p.name, g.good
  FROM person_wants_good
  JOIN person USING (pid)
  JOIN good USING (gid)

This will return the following table:

 name good
 ---- ---------
 john car
 john aircraft
 john bicycle
 paul motorbike
 paul ipad

It is then up to your client code to present this information in a cross-tabulated manner.

Upvotes: 3

Tudor Constantin
Tudor Constantin

Reputation: 26871

This is not a good DB design.

You should create an intermadiary table like:

table a_b
-----------
id_a int
id_b int

in this way you will successfully model a m to n relationship, so that a user can have multiple elements in his wishlist and an element to appear in multiple wishlists

Upvotes: 3

Bohemian
Bohemian

Reputation: 425418

This outputs up to 3 wishes (with nulls in wish columns when they don't have a wish)

select
  name,
  g1.goods as wish1,
  g2.goods as wish2,
  g3.goods as wish3
from tablea a
left join tableb g1.id on g1.wishlist1
left join tableb g2.id on g1.wishlist2
left join tableb g3.id on g1.wishlist3

This might be better though, and it's neater, if you don't mind a comma-delimited list of wishes:

select
  name,
  group_concat(goods) as wishes
from tablea a
left join tableb b on b.id in (a.wishlist1, a.wishlist2, a.wishlist3)
group by name;

which will output:

name  |  wishes
------|----------------------
john  |  car,aircraft,bicycle
paul  |  motorbike,ipad

Upvotes: 5

gbn
gbn

Reputation: 432662

SELECT
   a.name,
   b1.goods,
   b2.goods,
   b3.goods
FROM
   a
   LEFT JOIN
   b AS b1 ON a.wishlist1 = b1.id
   LEFT JOIN
   b AS b2 ON a.wishlist2 = b2.id
   LEFT JOIN
   b AS b3 ON a.wishlist3 = b3.id

If you have always 3 wishlist items, then this is just about OK. If you want to extend the number of wishlist items, then you'd be better with a separate table and store as rows. See First Normal Form

Upvotes: 3

Related Questions