Tried a couple of variation querys, but I keep getting the results when I do
"explain"
QUERY #1
explain select t.tagnumber,m.days,m.expdate from tags t
left join membership m on m.tagid = t.tagid
where t.orderid = '2';
QUERY #2
explain select t.tagnumber,m.days,m.expdate from tags t,membership m
where t.orderid = '2' and t.tagid = m.tagid
I get the results:
Impossible WHERE noticed after reading const tables
Not sure this has anything to do with it, but there is no data in either table
yet, we just set it up and are in the process of putting into production and the
end of the week. I just wanted to get the queries setup, make sure they're using
the correct index and such, so we can extract the required data when needed.
The orderid is what determines which what all should be extracted, from another
table, "customers" each account is created in a "customer" table, the orderid is
an auto increment type and this is what ties the "tags" and "membership" table
to the "customer"
>>On Wed, 5 Sep 2001 19:37:22 -0400, Ian Moore <[EMAIL PROTECTED]> wrote:
>>Doh! Just saw the "..orderid equals the tagid in both the.." Make that:
>>
>>select tagnumber,days,expdate from tags
>> left join membership on membership.tagid = tags.tagid
>> where orderid = membership.tagid
>>
>>there are a few other ways to do it. The most efficient depends on which
>>table has the most records, how many orderids match the tagid in the same
>>table .. etc...
>>
>>
>>On Wed, Sep 05, at 07:26pm, Ian Moore ([EMAIL PROTECTED]) wrote:
>>
>>> There may be a better way, but right off the top of my head I'd say:
>>>
>>> select tagnumber,days,expdate from membership
>>> left join tags on membership.tagid = tags.tagid
>>> where tags.tagid is not null
>>>
>>>
>>> On Wed, Sep 05, at 06:10pm, [EMAIL PROTECTED] ([EMAIL PROTECTED]) wrote:
>>>
>>> > Hello All,
>>> >
>>> > I got a sql query I seemed to be missing the obvious or just can't see.
>>> > Database Table structure:
>>> > CREATE TABLE `membership` (
>>> > `tagid` int(8) NOT NULL default '0',
>>> > `startdate` date NOT NULL default '0000-00-00',
>>> > `expdate` date NOT NULL default '0000-00-00',
>>> > `days` smallint(4) NOT NULL default '0',
>>> > PRIMARY KEY (`tagid`),
>>> > KEY `days` (`days`)
>>> > )
>>> >
>>> > CREATE TABLE `tags` (
>>> > `tagid` int(8) NOT NULL auto_increment,
>>> > `orderid` int(8) NOT NULL default '0',
>>> > `finderid` int(8) NOT NULL default '0',
>>> > `tagnumber` varchar(10) NOT NULL default '',
>>> > PRIMARY KEY (`tagid`),
>>> > UNIQUE KEY `tagnumber` (`tagnumber`),
>>> > KEY `orderid` (`orderid`,`finderid`)
>>> > );
>>> >
>>> > What I need to do is extract all the tagnumbers,days and expdate where the
>>> > orderid equals the tagid in both the "tags" and "membership" tables.
>>> >
>>> > I just can't seem to get the results I need. Any help would be much appreciated.
>>> >
>>> > Mysql version 3.23.40
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Tel: 1(225)686-2002
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
