SELECT from multiple tables

Anon

I can't help think this should be real easy but I can't for the life of me get it to work.

I was to display all the records from all tables with the same field specifies

i.e. Display all records where the name is equal to "paul" from all the tables in the database links.

I would have thought

SELECT * FROM news, dvd, shopping where create='paul g'

this just reports an ambigous error, and if I enter:

SELECT * FROM news, dvd, shopping

if returns thousands of records, hundred's of duplicates.

What am I doing wrong???

regards, pgudge

lawnmowerman

You have to tell the SQL server how to determine the match...

SELECT * FROM news, dvd, shopping WHERE news.create = 'paul g' AND dvd.create = 'paul g' AND shopping.create = 'paul g'

This should return the info you're asking for.

The create column must be in each table, or you'll have to find some other column which is common between all tables.

Hope this helps!

-Rich

Anon

Cheers,

I entered:

select * from dvd, news, warez where dvd.creator='paul g' and news.creator='paul g' and warez.creator='paul g'

and it still return duplicate matches, it returns every record 7 times.

regards

lawnmowerman

Have you made sure you don't have duplicate records in your tables?

-Rich

Anon

Yep, no duplicates, there are 9 records in dvd, 5 records in news, and 9 records in shopping but the query returns 315 records???

ah66533

Try this:

select d.creator, n.creator, w.creator from dvd d, news n, warez w WHERE d.creator = 'paul g' AND n.creator = 'paul g' AND w.creator = 'paul g'

lawnmowerman

Can you the portion of code which makes the query and then outputs the results? There should be no reason why the SQL server is reporting multiple rows if there is no duplicated data.

-Rich

Anon

This just returned the names 'paul g' in all columns 🙁

But thank you very much for your time,

I have decided to do one of my infamous while loops.

One other question.

My database is called 'links' and inside I have x amount of tables. Would it be better this way or one single table, and then add another field for the link category???

regards,

pgudge

watski

What actually happens when you perform the query select * from dvd, news, warez is that the database creates a huge table with every possible combination of every row from every table in the query (called the cross product of the three tables). Say we have two single attribute tables A and B like this

A

a1
a2

B

b1
b2

select * from A, B would give the rows

a1 b1
a1 b2
a2 b1
a2 b2

Rows are then eliminated using the from clause, eg. we can eliminate all rows not having an a1. This would still yield two rows, even though there are only one a1 in the A-table. The trick is to "tie all rows together". This is what Arto did in his reply, study it. This is why you got several rows for each DVD. Go with Arto's solution. If you try to use a while-loop to eliminate duplicates I will come over and slap you personally ;-) It is much better to understand what goes wrong and fix it rather than to patch things afterwards.

Johan

pgudge

🙂

I have implemented a while loop and it does a "SELECT * FROM $varname" for every table.

This is working fine. It's been working perfectly since I wrote it.

What I wanted from the query was.

I have (currently) 14 tables, "dvd, news, warez, shopping, books, etc..) all with 6 fields (id, name, url, creator, date, notes) I wanted to do a query where it would check every table and return a result (with six columns) with every record createrd by a person (creator='paul g') So that I could just print the result to the screen.

I will give Alto's solution ago and see what happens but the while loop wap is easier.

Would it be better overall it I had just one table and an extra field with the category name in, so then it would just be:

"SELECT * FROM links where creator='paul g'"

???

regards,

pgudge