Printable Version of Topic

Click here to view this topic in its original format

HTMLHelp Forums _ Databases _ Mysql joins

Posted by: Brian Chandler Sep 24 2006, 04:53 AM

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

Posted by: Sparkyg Sep 29 2006, 05:24 AM

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

Posted by: Brian Chandler Sep 29 2006, 11:12 PM

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.)



Posted by: Sparkyg Sep 30 2006, 02:27 AM

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

Posted by: Brian Chandler Sep 30 2006, 12:42 PM

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?


Posted by: Sparkyg Sep 30 2006, 04:06 PM

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

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)