Hi.
On Mon 2002-07-08 at 22:54:09 -0700, [EMAIL PROTECTED] wrote:
> I'm trying to get the name of a booth or tradeshow depending on the
> customer_link_type (which is an ENUM) combined with the
> customer_link_table_id which tells me the index/id of the correct table
> to look in. I've tried this SQL command, but it doesn't work right. I
> get multiple permutations still.
>
> Is this even possible to do with mySQL? Or do I have to make two queries
> (one for 'booth' and one for 'tradeshow' and store them in a PHP array
> or something)
In MySQL 4.x you can use UNION to accomplish what you want, in earlier
versions you have to do two queries and do some application-side work
or if you want the ORDER BY done by the database, you have to use a
TEMPORARY TABLE.
I think the problem could be avoided if the database design would be
normalized further. Considering your select, it could be that a
intermediate table, containing reference to "type", "name" and "id"
(and maybe others) would help and the both and tradeshow tables would
only contain the information unique to them.
Greetings,
Benjamin.
> SELECT customer_id, customer_name, customer_link_type,
> customer_link_table_id, tradeshow_name, booth_name
> FROM Customer_Table, TradeShow_Table, Booth_Table
> WHERE ((customer_link_table_id = tradeshow_id AND customer_link_type =
> 'tradeshow') OR (customer_link_table_id = booth_id AND
> customer_link_type = 'booth'))
> AND customer_mail_list = 1
> ORDER BY customer_date DESC, customer_link_type
>
> Here are the three relevant tables and fields (some removed for space
> saving):
>
> CREATE TABLE Customer_Table (
> customer_id INT(10) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY
> auto_increment,
> customer_link_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL,
> customer_link_type enum("booth", "tradeshow") NOT NULL,
> customer_name varchar(30),
> customer_mail_list tinyint(1) UNSIGNED DEFAULT '0',
> );
>
> CREATE TABLE TradeShow_Table (
> tradeshow_id int(10) unsigned NOT NULL auto_increment,
> tradeshow_name varchar(100) NOT NULL default ''
> )
>
> CREATE TABLE Booth_Table (
> booth_id int(10) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY
> auto_increment,
> booth_name varchar(30) NOT NULL
> );
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
