temp table - How can I do this?

sgtpepper

How can I do the following, using a temp_table?

select from table 1 where id='1'
select from table 2 where id='1'

Then display the results from both queries?

stolzyboy

select table1., table2. from table1, table2 where table1.idfield = "1" and table2.idfield = "2"

sgtpepper

Hi Stolz,

This is producing duplicates. The query should show only a total of 66 records.

50 from one table and 16 from the other....right now it is showing 800.

It looks like they are being duplicated. Can something be added to the query to remove duplicates?

stolzyboy

DISTINCT - look it up at www.mysql.com

i may have the query wrong also where the WHERE isn't working correctly

also do you need all the fieldnames like with the table1.* or do you only need some fields, like table1.field1, table1.field2, table2.field1, table2.field2, etc...

sgtpepper

I dont need them all...just a few. I planned on being more specific with the fields once I knew the query was working.

stolzyboy

well then you could do something like

select table1.idfield, table1.otherfield, table2.idfield, table2.otherfield from table1, table2 where table1.idfield = "1" and table2.idfield = "1"

sgtpepper

Weird!

This still shows 800 results

select residential.lister_office_name, multi.lister_office_name from residential, multi where residential.lister_office_name = "Century 21" and multi.lister_office_name = "Century 21"

stolzyboy

if you have phpmyadmin, try pasting your query into that and see how many results come through that way

sgtpepper

Still no luck...

What if I try a temp_table??

CREATE TEMPORARY TABLE temp_union select from residential where lister_office_name='Century 21 Highview Realty';
INSERT INTO temp_union select from multi where lister_office_name='Century 21 Highview Realty';
SELECT * FROM temp_union;
DROP TABLE temp_union;

The error I get is
ERROR 1136: Column count doesn't match value count at row 1

stolzyboy

maybe use ( and ) to enclose your subselect lines so the insert and create table aren't confused

sgtpepper

I was able to get the query to work in mysql. Some advise...

How should it be placed here?

$query="CREATE TEMPORARY TABLE temp_union select lister_office_name from residential where lister_office_name='Century 21
Highview Realty'
INSERT INTO temp_union select lister_office_name from multi where lister_office_name='Century 21 Highview Realty'
SELECT * FROM temp_union";

stolzyboy

how exactly did you get it to work in mysql, maybe try and separating it into a couple queries