On Thursday, August 14, 2003, at 08:04 PM, Jennifer Goodie wrote:
I have 2 tables used for an online calendar...
first table fields: primary_key , start_date, event_name, event_description second table fields: primary_key, end_date
Tables fields are shortened and can't be changed.
My second table only contains events that have a end date. I want
to create
a query that will take all the fields in. If no end_date exists
then set to
NULL. Been playing with it all day. Hoping some advance function exists. I
thought of using a temp table but there must be a better way.
I am confused by your question. It think it is missing words. If you are
trying to select all records from first_table that do not have a record in
second_table you can use a left join and is null...
SELECT * from first_table LEFT JOIN second_table USING (primary_key) WHERE
second_table.primary_key IS NULL
Maybe I'm missing something here, but I don't see why you would want to
split your tables up that way. You can't be saving that much room, and I
don't think it really goes with standard normalization conventions.
If you don't want to join the two, you could use the query above to find out all of the event ID's that you need to add to the second field. The easiest way to deal with this would be to modify Jennifer's to:
SELECT first_table.primary_key from first_table LEFT JOIN second_table USING (primary_key) WHERE
second_table.primary_key IS NULL
and export it, and then just straight import it into your second_table. If you leave what you're putting into the second_table.end_date blank, it should come through as NULL.
Although, I would also definitely suggest joining the two tables. FWIW, you can do this fairly easily by:
CREATE TABLE new_table
SELECT first_table.primary_key AS primary_key,
first_table.start_date AS start_date,
first_table.event_name AS event_name,
first_table.event_description AS event_description
second_table.end_date AS end_date
FROM first_table LEFT JOIN second_table USING (primary_key);Although this is using extremely redundant syntax, this should give you one table, where all of the end_dates that's aren't populated in second_table end up being NULL. From there you could run a query:
SELECT * FROM new_table WHERE end_date IS NULL;
Good luck, -Cameron Wilhelm
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
