List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:July 9 2002 5:54am
Subject:Conditional join SQL syntax help?
View as plain text  
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)

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


Thread
Conditional join SQL syntax help?Daevid Vincent9 Jul
  • Re: Conditional join SQL syntax help?Benjamin Pflugmann23 Jul