The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

> Mysql joins, Using a many-many relation table
Brian Chandler
post Sep 24 2006, 04:53 AM
Post #1

Jocular coder

Group: Members
Posts: 2,212
Joined: 31-August 06
Member No.: 43

Any DB experts here?

I can't understand almost all of the MySQL manual's explanation relating to *join*. So I'm looking either for an answer to my problem, or (preferably) a reference to a good technically clear description. (What I mean by technically clear is that it doesn't matter how complicated something is, if you define terms, and then use them consistently. In the MySQL join stuff, the word "natural" for example is used to mean, roughly, "Uh, where was I? Well, you know...")

I have a table of puzzles: many columns, each a property of the puzzle identified by the code that is the primary key column. Very straightforward and simple.

I also have a many-many table, relating puzzles to topics. One puzzle may have more than one, and obviously each topic pertains to many puzzles.

I want to extract the set of puzzles having a topic or topics. For one (e.g. 'fish'), this is simple:

SELECT * FROM puzzle, topic WHERE puzzle.pcode = topic.pcode AND topic.tcode = 'fish';

(pcode is the puzzle identifier, tcode is the topic identifier)

But suppose I want to find puzzles on 'fish' or 'beans'? I need only the distinct rows from the puzzle table, but I can't understand the scope of the DISTINCT keyword - unless I can restrict it to apply to the puzzle table only, it will obviously claim that "puzzle 1 relating to fish" and "puzzle 1 relating to beans" are distinct.

Grateful for any assistance. wink.gif unsure.gif

Brian Chandler
Nothing in this post constitutes "commercial solicitation". PayPal does not solicit residents of Japan. Contents may settle in transit. "Legal mind" may or may not be brain-damaged.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Posts in this topic

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:


- Lo-Fi Version Time is now: 31st July 2014 - 08:42 PM