List:General Discussion« Previous MessageNext Message »
From:Benjamin Pflugmann Date:July 23 2002 6:48pm
Subject:Re: Conditional join SQL syntax help?
View as plain text  
Hi.

On Mon 2002-07-08 at 22:54:09 -0700, daevid@stripped 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
> );

-- 
benjamin-mysql@stripped
Thread
Conditional join SQL syntax help?Daevid Vincent9 Jul
  • Re: Conditional join SQL syntax help?Benjamin Pflugmann23 Jul