CODEWITHSUNDEEP
mysql
user1831430
user1831430

Reputation: 83

Having trouble with simple inner join in mysql

So I basicly have a database that looks like this:

card_types
id    description    price
1     Card1           1.00
2     Card2           2.00
3     Card3           3.00

card_occasions
id    occasion
1     birthday
2     graduation
3     thank you
4     other

type_occasions
ID    TypeID   OccasionID
1     1        1
2     1        2
3     1        4
4     2        3

I am trying to do an inner join using WHERE card_type.ID = 1 resulting in a similar output but I have no idea where to begin.

Example output:

card_occasion.ID   card_occasions.Name
1                  birthday
2                  graduation
4                  other

Any help would be appreciated. Thanks.

Upvotes: 0

Views: 70

Answers (4)

Whome
Whome

Reputation: 10402

And if you really want to link all three tables for reason we don't see here you may use this method. This starts linking from the type_occasion table to appropriate "basic data" tables.

Select
  typeo.OccasionID, co.occasion as Name
From type_occasion typeo
  JOIN card_type ct ON (typeo.TypeID=ct.id)
  JOIN card_occasion co ON (typeo.OccasionID=co.id)
Where
  typeo.TypeID=1
  -- ct.id=1

Upvotes: 1

Zeemee
Zeemee

Reputation: 10714

Since type_occasions already owns the typeid, you don't need to join the type table.

SELECT o.id, o.occassion
FROM card_occasions o
INNER JOIN type_occasions t ON t.occassionid = o.id
WHERE t.typeid = 1

Upvotes: 1

Sanal K
Sanal K

Reputation: 733

SELECT A.id,A.occasion FROM card_occasions A JOIN type_occasions B ON B.OccasionID= A.id AND B.TypeID=1

Upvotes: 1

Donovan Charpin
Donovan Charpin

Reputation: 3397

You begin with the table where you want values. After Join the table you need to make the relation.

SELECT card_occasions.id, card_occasions.occasion
FROM card_occasion co
INNER JOIN type_occasions to ON (to.OccasionID = co.id)
                                               ^ the relation between two table
WHERE ct.ID = 1

Upvotes: 1

Related Questions