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,298
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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Reply to this topicStart new topic
post Sep 30 2006, 04:06 PM
Post #2


Group: Members
Posts: 46
Joined: 29-September 06
From: Suffolk UK
Member No.: 270

Hi Brian

All looks and sounds good to me, I was half expecting a table with a hundred or so fields lol. As long as you are passing by reference then there should be no problems.

Catch ya later

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: 19th June 2018 - 09:16 PM