How to Query 2 Tables (Not a join?)

[MichelleStillCantType]

Uuuuughhhh! Frustration. This seems like it should be so simple and I'm lost.

I want to randomly rotate (with each load or refresh) a section of text, corresponding images and links, etc. with a limit of 1 on a specific webpage. (The text I show on this page is also limited by a "WHERE" statement).

All is great if I only query from one table. Simple enough...

SELECT * FROM tableA WHERE level= '1' ORDER BY reverse(rand()) LIMIT 1

I randomly get my info from tableA with each refresh just fine with the above code.

BUT...

I also need to select from tableB, tableC, tableD and so on. All fields are the same in tableA, tableB and so on. I've tried...

SELECT * FROM tableA,tableB,tableC WHERE level= '1' ORDER BY reverse(rand()) LIMIT 1

...amongst other things. I'm just not getting it. Is something that seems so simple impossible?

Only one section of text can show at a time and this will be from up to 50 tables if I get it worked out.

I do SOME programming. Minimal. Enough to do what I need here and there (like not having to update 100's of html pages for one little alphabetical addition) but you wouldn't call me a programmer. Help? In simple terms..OR even exact code

[LobsterWebDesign]

I would write a script to choose the table first by random and so put your table names in a seperate table and then use your sql to pick the table name. Hold this in a variable and use it in your current script.

[Dubbya]

Tech republic has a concise little article on how to do this with or without Joins or unions. You should be able to find a select statement you can use.

Check it out:

SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column1 = table2.column1;

SQL basics: Query multiple tables

[rgcote]

Hmmm. Why do you need all those tables? If the structure is the same between all the tables, then maybe you'll have an easier time by combining all the data into a single table and add an additional field that marks each record as "A", "B", "C", etc.

I am assuming that you put them in different tables because you need to track the records in each table separate from the records in the other tables. If that is the case, then combining them into a single table and tagging them appopriately might be a better way to go.

[imvain2]

Dubbya has the correct query structure, however rgcote has a great point. I do exactly what he is talking about and it works great.

[kgun]

Not a join ? (Note the question mark).

I take the idea of creating a temporary table, but joins are an advanced SQL topic.

Why make it complicated when you can make it simple?

[MichelleStillCantType]

Thank you all.

Dubbya, I have been trying that and just can't figure out what I'm doing wrong because it wont work for me. I have pulled out half my hair...lol.

The reason I have so many tables with all the same fields is that eventually (future expansion) after some growth, thesee tables will have fields added that are NOT the same.

On another site I did it with just the one table and called up needed info by a category field - but - then when the many different fields were added for the many different categories it ended up becoming a pain in the you know for many reasons. I'd rather have the different tables if I can get it to work.

[Syspira]

If you're trying to do a multi-table select by using the simple syntax (WHERE a.col1 = b.col1 etc.) and it's not working, check to see if you have any null values in any of the columns you're trying to select. That can sometimes cause that sort of query to fail.

If that is the case, you need to use a Join instead.

This has tripped me up on more than one occasion, but Join (usually a Left Join, with the most important table listed to the left, i.e. first) generally sorts it out.

[kgun]

Note, you are working on a relational database. Then any complete overlapping of fields (columns) in the tables is wasted work.

[jammybiskit]

One way to do this is to use the union statement which is standard in most modern SQLs. The order by clause comes after all the unions. For two tables tablea and tableb this looks like:

select * from tablea where level=1 union select * from tableb where level=1 order by reverse(rand()) limit 1;

For three tables a,b,c:
select * from tablea where level=1 union select * from tableb union select * from tablec where level=1 order by reverse(rand()) limit 1;

and so on

The tables must all have the same columns for this to work.

This works on MySQL but is probably not efficient - it may hit your server hard if you have as many as 50 tables. You don't say how large these tables are or are expected to become.

You should consider amalgamating the tables into one with an extra column to distinguish between a,b,c and so on as rgcote suggested. You may need to do this if the performance is poor.

[kgun]

Originally Posted by jammybiskit

You should consider amalgamating the tables into one with an extra column to distinguish between a,b,c and so on as rgcote suggested. You may need to do this if the performance is poor.

Yes that is a possible solution, like pulling fields / records from the table with PHP or another scripting language.

1. What is the minimalistic solution that should be learned sooner or later?
2. If (s)he can not use some type of joins, I question the structure of the database (collection of tables).

Why not ask on the MySQL forum?

[lyricsenergy]

1. temporary table2. temporary array
3. ?

[mccreath]

"The reason I have so many tables with all the same fields is that eventually (future expansion) after some growth, these tables will have fields added that are NOT the same."

A structure that might work for you is to move all the common fields onto a base table and only have the custom fields in the separate tables (a,b,c). You can then do the single query for your random search while still allowing you to customise each type. e.g.

#searching just a
select * from a, base where a.baseid = base.id ...

# searching all
select * from base ...

[jammybiskit]

"The reason I have so many tables with all the same fields is that eventually (future expansion) after some growth, thesee tables will have fields added that are NOT the same."

Using * (the catch all columns identifier) will not work with union if the tables have different columns.

Assuming that each of your 50 tables has a column called 'level' and another called 'result', say, (maybe with some other columns as well), you can use the statement

select result from tablea where level=1 union select result from tableb union select result from tablec where level=1 ... order by reverse(rand()) limit 1;

This is going to get large and difficult to maintain for 50 tables.

Use a single table instead of having 50 - which would mean a much simpler select. You can add columns to a table in MySQL and others using 'alter table'.

If you had a single table then your statement would be

select result from mytable where level=1 order by reverse(rand()) limit 1;

Adding columns would not affect this statement.