[Resolved] Simple Join?

[slevytam]

Hi,

I am trying to do a join on three tables. The problem is that I don't have a key that I would like to join on. Basically, the three tables are similar. I would just like to be able to get a list of all the records in all three tables.

Can anyone help?

Thanks,

SL

[ultraslacker]

Table schema would be helpful.

If the tables are similar, than you would probably be better off collapsing them into one table if possible rather than doing a cross join.

[slevytam]

Sorry, my internet has been out.

I will try and post the schema this evening.

Thanks for your help!

Shalom

[slevytam]

Hi,

Sorry for the late reply.

I have three tables.

they look something like this

snid | usid | title | description | etc.
pnid | usid | title | description | etc.
tnid | usid | title | description | etc.

what i would like to end up with is this

snid | pnid | tnid | usid | title | description | date

the values might look like this
Null | 234 | Null | 879 | title | description | date
356 | Null | Null | 879 | title | description | date

so I will have all the records in each table and then will be able to determine the type by checking which id is not null.

i can't use 1 table because these tables will get huge and I can't afford to have them collapsed.

Thanks!

Shalom

[ahundiak]

What you want to do is to UNION the tables.
http://dev.mysql.com/doc/mysql/en/UNION.html

But keep in mind, you really should just use one table. Maybe if you have billions of records then you might run into a problem but otherwise there is really no point is using three.

[slevytam]

Hi,

Thanks for your reply,

These records will have long descriptions(couple pages of text). If I have 10 thousand records won't this be a huge drain?

Thanks,

Shalom

[ahundiak]

These records will have long descriptions(couple pages of text). If I have 10 thousand records won't this be a huge drain?

Not really. If you end up searching the description then you need to search all three tables anyways. Databases are pretty smart. They won't load in columns unless they really need to.

Try to write out the queries for all the things you want the system to eventually be able to do. I think you will find having three tables will become a real pain. And consider what would happen if you ever needed to add a fourth category.

Finally, if things ever reached a point where you absolutely had to break things up into multiple tables then you can use views and stored procedures to make your application think that there is still just one table.

[slevytam]

Ok,

I'll look into that.

Thanks for your help!

SL