I have two tables in a database called info table one is called fred, table two is called george. both tables have the same feilds and feild names. I need to JOIN the two to create a search that looks at both tables. so far so good. the tables do NOT have any feilds that contain the same data. so

SELECT * FROM info.fred LEFT JOIN info.george ON (info.fred.feildname = info.george.feildname) ;

won't work....as no feilds contain the same data

so....I tired

SELECT * FROM info.fred LEFT JOIN info.george ON (info.fred.feildname <> info.george.feildname);

But that seemed to really piss off the server. (these tables are about 5000 lines each with about 256 feilds each) The feild name I used contained a alphanumeric value....

Both tables have a feild named ID. The basic idea here is that a seacrh is performed by th euser, and the ID of the search is saved in a session. The session is then passed to a new statment that requires both tables be seacrhed for those ID values. So the whole statment would end up like:

SELECT * FROM info.fred LEFT JOIN info.george ON (info.fred.feildname <> info.george.feildname) WHERE ID = 'variable';

I suppose what i'm really looking for here is to "stack" the tables on top of each other...as opposed to joining them left or right.

I'm off rtfm now....hopefully i can find it in there, but jic i cant any help would be appreciated....

    hi,

    Noit sure what you are doing with to tables with all the same field/ I think you should, from a proper database design perspective, consider combining the two tables into one, and then add a colum where you seperate between fred and george.

    I do not think you can do this in one query, and have to wuery each table individually. You could try:

    select * from table1, table2 where table1.id = $id or table2.id = $id.

    I am afraid this will not give you anything decenthough..

    J.

      Yes, I would agree. That is not currently am option though.

        Then I am afraid you are facing using two queries to get all the data..?

        Unless somebody else has a clever way to do this? Since there are no corrsponding fields you cannot just join the tables: THey have to have a field in common (Where I mean that the values in the field link records. For you an id of 1 in one table has no meaning in the other..)

        J.

          SELECT * FROM info.fred LEFT JOIN info.george ON (info.fred.feildname <> info.george.feildname);

          But that seemed to really piss off the server. (these tables are about 5000 lines each with about 256 feilds each)

          That would piss off a server - and any users unlucky enough to be on it at the time, as you are generating a huge join, assuming some cardinality.

          Why 256 fields each? How do the tables relate? We wouldn't be using a denormalized design then running into trouble with sql, would we?

            I'm not sure why there are 256 feilds....I assume that the data comes out of a MS product. There are two tables becuase the data comes from different places via ftp.....i guess thats why they were set up that way anyway...not really sure.

              Write a Reply...