--- Erik Meyer <emeyer@xxxxxxxxxxxx> 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
mark_weinstock@xxxxxxxxx
***************************************
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
