List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 5 2000 12:51am
Subject:Re: SQL question
View as plain text  
At 7:32 PM -0500 2000-02-04, sam1600@stripped wrote:
>Hi,
>
>Is there better query than what I'm doing?
>The following query works, but I came to
>it by trial and error and was wondering first
>of all if its correct SQL and also if there may
>be a better way to do it.
>
>I have slightly modified the following tables for clarity.
>
>The query retrieves the topic.name(s) with ONLY the
>supplied packages.topic_blockID
>
>SELECT topics.name
>FROM topics, topics_block, packages
>WHERE packages.topic_blockID = 136
>AND packages.topic_blockID = topics_block.ID
>AND topics.ID = topics_block.topicID
>
>skiing
>sightseeing
>bird watching


Looks okay, but you might want to rename your columns
to be more consistent with the way you're using them.
As it is, you're using topic_blockID and ID to mean blockID,
and you're using ID to mean both ID and block ID.  Makes
it harder to understand your query.  Is there a reason not
to have your tables like this:

packages:
	ID
	blockID (was topic_blockID)
	name
topics:
	ID
	name
topics_block
	blockID (was ID)
	ID (was topicID)

Then your query would be:

SELECT topics.name
FROM topics, topics_block, packages
WHERE packages.blockID = 136
AND packages.blockID = topics_block.blockID
AND topics.ID = topics_block.ID

That's easier to read, to me at least, because you're
joining on columns with the same names.

>
>Thanks,
>Sam
>
>
>
>mysql> select * from packages;
>+----+----------------+-------------------+
>| ID | topic_blockID  | name              |
>+----+----------------+-------------------+
>|  1 | 136            | this package name |
>+----+----------------+-------------------+
>
>mysql> select * from topics;
>+----+---------------+
>| ID | name          |
>+----+---------------+
>|  1 | skiing        |
>|  2 | boating       |
>|  3 | sightseeing   |
>|  4 | cruising      |
>|  5 | hiking        |
>|  6 | bird watching |
>+----+---------------+
>
>mysql> select * from topics_block;
>(last five records shown here)
>+-----+---------+
>| ID  | topicID |
>+-----+---------+
>| 134 |       2 |
>| 134 |       4 |
>| 136 |       1 |
>| 136 |       3 |
>| 136 |       6 |
>+-----+---------+
>
>
>
>----------------------------------------------------------------
>Get your free email from AltaVista at http://altavista.iname.com
>
>--
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail mysql-thread26709@stripped
>
>To unsubscribe, send a message to:
>     <mysql-unsubscribe-paul=snake.net@stripped>


-- 
Paul DuBois, paul@stripped
Thread
SQL questionsam16005 Feb
  • Re: SQL questionPaul DuBois5 Feb