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,460
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
Replies
Sparkyg
post Sep 30 2006, 02:27 AM
Post #2


Member
***

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



Hi There

If you have to write these selects all the time, you could write a helper function that returns all the column headings for a given table to use at design time, this way you could save the hastle of having to type all the column names.

How many columns does your puzzle table have? There may / or may not be a better way to organize your database.


Cheers

Sparky
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Sep 30 2006, 12:42 PM
Post #3


Jocular coder
********

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



QUOTE(Sparkyg @ Sep 30 2006, 04:27 PM) *

Hi There

If you have to write these selects all the time, you could write a helper function that returns all the column headings for a given table to use at design time, this way you could save the hastle of having to type all the column names.

How many columns does your puzzle table have? There may / or may not be a better way to organize your database.


+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| pcode | varchar(10) | | PRI | | |
| boxcode | varchar(16) | YES | | NULL | |
| title | varchar(50) | YES | | NULL | |
| jtitle | varchar(100) | YES | | NULL | |
| artist | varchar(30) | YES | MUL | NULL | |
| pcs | int(5) | YES | | NULL | |
| weight | int(5) | YES | | NULL | |
| ldim | int(5) | YES | | NULL | |
| sdim | int(5) | YES | | NULL | |
| orient | char(1) | | | | |
| box | char(1) | | | | |
| price | int(5) | YES | | NULL | |
| home | varchar(20) | YES | | NULL | |
| status | varchar(5) | YES | | NULL | |
| memo | varchar(50) | YES | | NULL | |
| feat1 | bigint(20) | | | 0 | |
| feat2 | bigint(20) | YES | | 0 | |
| stock | int(5) | YES | | NULL | |
| purch | int(5) | YES | | NULL | |
| avail | char(3) | YES | | NULL | |
| acq | date | YES | | NULL | |
| disc | int(2) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+

Uh, looks like a couple of dozen. A few aren't actually used, and one or two are "sparse" ( the "boxcode" is for special cases when the manufacturer's code can't be computed from my code in the regular way; I could have used a separate table for this). One factor is that I have no idea really how heavy a load a trip to the db server represents. I pass php associative arrays around, so functions get immediate access to all properties of a puzzle - this sounds like an efficient way to do it, but who knows?

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: 28th March 2024 - 01:16 PM