Actually, version 4.0 will. I'll have to wait til then but for now, I'll
use you other advice. Thanx
- Deryck H
- http://www.comp-u-exchange.com
----- Original Message -----
From: "Loyd Goodbar" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, September 13, 2001 10:22 PM
Subject: Re: using LEFT JOINS
Duh on me... what about this (may not work with MySQL, does it support
unions
yet?)
insert into mastertable (user,name,info)
(select * from one where user='loyd'
union
select * from two where user='loyd'
union
...)
repeat to table 15. Since 15 tables have the same structure, this will work.
Loyd
On Thu, 13 Sep 2001 20:19:14 -0500, Loyd Goodbar <[EMAIL PROTECTED]>
wrote:
>On Thu, 13 Sep 2001 19:55:11 -0500, "Deryck Henson" <[EMAIL PROTECTED]>
>wrote:
>
>>OK, first of all, brilliant and well thought out. One problem, every one
of
>>my tables are EXACTLY the same. All I want to do is take the records from
>>all 15 of the tables where the username(column) in them is what I say.
When
>>I try to do this, it gives me an error that says it's ambiguous. So I
need
>>to merge all the tables (and I dont mean the MERGE statement) together to
>>create a sort of one big master table but it still knows what table each
>>record is from. Hopefully that clears things up a bit, and I do
>>appreciate that long yet excellent explanation on LEFT JOINS. Thank you
and
>>please, if you have an idea of what I mean, please tell me an answer.
>>
>>- Deryck H
>>- http://www.comp-u-exchange.com
>
>Ouch. I don't know off the top to achieve that easily, and I'll explain
why...
>SQL does not "stack" results, which is what I believe you're asking for.
Let
>me see...
>
>I have table ONE:
>user,name,info
>loyd,Loyd,hello
>joe,Joe,hi
>
>and TWO:
>user,name,info
>loyd,Loyd G,hello2
>joe,Joe P,hi2
>
>So what I gather you want is this result (query on "loyd"):
>user,name,info
>loyd,Loyd,hello
>loyd,Loyd G,hello2
>
>The problem is SQL won't stack the results like the above, but will do
>something like this:
>user,name,info,user,name,info
>loyd,Loyd,hello,loyd,Loyd G,hello2
>
>But what you're running into is a query like this
>select * from one left join two on (one.user=two.user) left join three on
>(one.user=three.user) ... where one.user='loyd'
>
>That would give the above result, except the field user, name, and info are
>defined more than once. SQL doesn't know WHICH user, name, and info you
want
>since they're defined multiple times. This leads to the ambiguous column
>error.
>
>You could do something like
>select one.user as user1, one.name as name1, one.info as info1, two.user as
>user2 ...
>but that won't stack the results like you expect.
>
>The only clean way I know is to do this programmatically with multiple
>statements...
>insert into temptable (user,name,info) (select user,name,info from one
where
>user='loyd')
>insert into temptable (user,name,info) (select user,name,info from two
where
>user='loyd')
>and so on, then
>select * from temptable
>
>That will give you stacked results, but is not a single SQL statement. I
truly
>believe that is a very complex selection (especially over 15 tables!), and
is
>one I haven't needed to do yet. One good thing about the above is you can
>insert arbitrary data, such as this...
>
>insert into temptable (user,name,info,comment) (select user,name,info,'from
>table one' from one where user='loyd')
>
>Devshed (www.devshed.com) I think had a MySQL article recently on creating
SQL
>crosstabs. However, crosstabs give summary information, not detail.
>
>How often would the master table need to be (re)created? Are you wanting a
>realtime table, or an occasional reporting table? If this is only
>occasionally, it would be a good candidate for stored procedures, if/when
>MySQL supported them. If this is a realtime table, your only real hope
>(currently unsupported with MySQL) is a trigger. (A program that acts on
data
>as it is inserted, updated, or deleted from a table. It is called by the
>database engine without requiring user intervention. In this case, an
>after-insert trigger could copy the data from the one...fifteen table to
the
>master table automatically.)
>
>I hope I understood your request, and maybe the above will lead you in the
>right direction.
>
>Loyd
>
>--
>"How much would you pay for your life?"
>"More than I would take to give it up."
>[EMAIL PROTECTED] ICQ#504581 http://lgoodbar2.pointclark.net/
--
"How much would you pay for your life?"
"More than I would take to give it up."
[EMAIL PROTECTED] ICQ#504581 http://lgoodbar2.pointclark.net/
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
