--- Erik Meyer <[EMAIL PROTECTED]> wrote:
> Hope someone can help me.
> 
> I have two tables:
> 
> evt_details
> evt_sponsors
> 
> The structure is as follows:
> 
> evt_details                                                   evt_sponsors
> 
> evt_detail_id         (INT)(PK)                       evt_sponsor_id (INT)(PK)
> evt_detail_title              (CHAR)                  evt_sponsor_name (CHAR)
> evt_sponsor_id1               (INT) NON-NULL
> evt_detail_date               (date)
> evt_sponsor_id2               (INT) NULL
> evt_sponsor_id3               (INT) NULL
> evt_sponsor_id4               (INT) NULL
> 
> Now, the event can have either 1 sponsor, 2 sponsors, 3 sponsors,
> or 4
> sponsors.
> 
> Is there a way where I can return a result whether it has 1, 2, 3,
> or 4
> since some of the events will  not have a 2d,3d, or 4th sponsor, a
> combination of 1 & 2, 1,2,3, or all 4 depending on the values in
> the table?

You _could_ normalize the data a bit more and use a lookup table with
a separate line for each event/sponsor combination.

The event table would have event details, the sponsor table would
have sponsor details, and the lookup table would simply be a cross
reference using the IDs. Something like this:

Evt_Sponsor:
event_id   sponsor_id
    1           1
    1           2
    1           3
    1           4
    2           1
    2           2
    2           3
    3           3
    3           4
    4           2

This shows four events: one with 4 sponsors, one with 3, one with 2,
and one with 1. Then you can use the lookup table to query on the
events/sponsor combiantions that exist.

> 
> Here is the query I have:
> 
> SELECT e.evt_detail_title, a.evt_sponsor_name, b.evt_sponsor_name,
> c.evt_sponsor_name,
>        d.evt_sponsor_name
> FROM evt_sponsors a, evt_sponsors b, evt_sponsors c, evt_sponsors
> d,
> evt_details e
> WHERE e.evt_detail_date <= DATE_SUB(NOW(), INTERVAL 1 DAY) AND
> e.evt_sponsor_id1=a.evt_sponsor_id AND
>       (e.evt_sponsor_id2=b.evt_sponsor_id OR e.evt_sponsor_id2=0)
> AND
> (e.evt_sponsor_id3
>        =c.evt_sponsor_id OR e.evt_sponsor_id3=0) AND
> (e.evt_sponsor_id4=d.evt_sponsor_id OR e.evt_sponsor_id4 =0)
> 
> 
> Thanks for the help,
> Erik W. Meyer
> 
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 


=====
Mark Weinstock
[EMAIL PROTECTED]
***************************************
You can't demand something as a "right" unless you are willing to fight to death to 
defend everyone else's right to the same thing.
***************************************


        
                
__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs  
http://hotjobs.sweepstakes.yahoo.com/careermakeover 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to