The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> 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,262
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
Sparkyg
post Sep 29 2006, 05:24 AM
Post #2


Member
***

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



Hi There

The scope of distinct is the unique rows in the results of your query.

The problem that you would have with your query is that you are doing " select * ", this is nearly always a bad idea. You need to specify only the columns that you want, otherwise DISTINCT will return a row for beans and a row for fish, leave tcode out of the select statement and you will only have one row.

cheers

Sparky


--------------------
If I have helped in any way then I would be grateful if you could Donate a photo to ' Free Photos for Websites ' millions of webmasters need your help :)

web design ipswich
web design essex
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Sep 29 2006, 11:12 PM
Post #3


Jocular coder
********

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



QUOTE(Sparkyg @ Sep 29 2006, 07:24 PM) *

Hi There

The scope of distinct is the unique rows in the results of your query.

The problem that you would have with your query is that you are doing " select * ", this is nearly always a bad idea. You need to specify only the columns that you want, otherwise DISTINCT will return a row for beans and a row for fish, leave tcode out of the select statement and you will only have one row.


Thanks. That's how it looked to me. Trouble is, selecting "just the columns you want" isn't very practical for a function that returns the complete puzzle array for use by various bits of the system. The only alternative to '*' is to list all the columns, which just seems a bit tiresome. (I hit this problem before, with "Orders", where one column is a MySQL-not-very-sensible datestamp, which I want to convert to a Unix timestamp, using "MySQL function() as unixtime" - I found I had to list explicitly every other column in the table.)




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


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


--------------------
If I have helped in any way then I would be grateful if you could Donate a photo to ' Free Photos for Websites ' millions of webmasters need your help :)

web design ipswich
web design essex
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 #5


Jocular coder
********

Group: Members
Posts: 2,262
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?



--------------------
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
Sparkyg
post Sep 30 2006, 04:06 PM
Post #6


Member
***

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

Sparky


--------------------
If I have helped in any way then I would be grateful if you could Donate a photo to ' Free Photos for Websites ' millions of webmasters need your help :)

web design ipswich
web design essex
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

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: 23rd November 2014 - 06:55 PM