0

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.

user1831430
  • 83
  • 2
  • 9
  • 1
    Can you post the query? – Shimon Rachlenko Oct 15 '13 at 07:21
  • Thats the problem I don't know what to do for the query. – user1831430 Oct 15 '13 at 07:22
  • You want to join all 3 tables? – Shimon Rachlenko Oct 15 '13 at 07:22
  • I just want the results listed above using the WHERE card_type.ID = 1. I'm pretty sure that would require joining all three tables in some way or another. – user1831430 Oct 15 '13 at 07:23
  • possible duplicate of [using where and inner join in mysql](http://stackoverflow.com/questions/1392479/using-where-and-inner-join-in-mysql) – Mosty Mostacho Oct 15 '13 at 07:24
  • Is this what you want SELECT co.id, co.name FROM card_types AS ct LEFT JOIN type_occasions AS to ON (ct.id = to.TypeID) LEFT JOIN card_occasions AS co ON (co.id = to.ID) WHERE ct.id = 1 – Deepak Rai Oct 15 '13 at 07:26
  • Your question contains some confusing typos. You should check if you spell table and field names consistently. However I think I know what you want :) - see my answer below. – Zeemee Oct 15 '13 at 07:30
  • This question is a good example how to NOT use Stack Overflow. A "question" like "Having trouble with simple inner join in mysql", and four nearly identical answers. – Zeemee Oct 15 '13 at 07:35

4 Answers4

1

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
Zeemee
  • 10,486
  • 14
  • 51
  • 81
  • I don't understand why it's not your answer which won points! – Donovan Charpin Oct 15 '13 at 07:37
  • Sorry about such a poorly written question. I am on a deadline with this project I'm working on and didn't have much time to write it out/explain it. I'm still learning this MySQL joining stuff. – user1831430 Oct 15 '13 at 07:44
1

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
Donovan Charpin
  • 3,567
  • 22
  • 30
1
SELECT A.id,A.occasion FROM card_occasions A JOIN type_occasions B ON B.OccasionID= A.id AND B.TypeID=1
Sanal K
  • 723
  • 4
  • 14
  • 1
    @user1831430 It's not about what is "working", but what answers your question the best. Your question was about "using WHERE card_type.ID = 1". – Zeemee Oct 15 '13 at 07:39
  • I saw this answer first. I didn't see the others until I refreshed. – user1831430 Oct 15 '13 at 07:47
1

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
Whome
  • 10,181
  • 6
  • 53
  • 65