A little fuzzy on Join

[envexlabs]

Hello,

 

I have a directory, that is pulling from 2 tables, a simple directory 'table', and a more complex 'store' table.

 

Right now, you can either choose to look at 1 table or the other. This is working fine.

 

I would like the user to be able to just see a master list of both mixed into one list.

 

I'm not sure if i can use join as i'm not trying to link up values from both tables, i would just like to grab all the info from both, sort by name and display to the user.

 

Sorry if this is confusing, but does anyone have any ideas?

 

Thanks,

 

envex

[fenway]

I believe you're looking for UNION.

[envexlabs]

thanks, i will look into this!

 

*edit*

 

I seem to be getting this error:

 

The used SELECT statements have a different number of columns

 

is there a way to grab the info even though the 2 tables are different in layout?

[envexlabs]

After reading the gudilines post, i have additional info:

 

CREATE TABLE `store` (
`id` int(100) NOT NULL auto_increment,
`contact_name` varchar(250) NOT NULL,
`username` varchar(200) NOT NULL,
`name` varchar(500) NOT NULL,
`email` varchar(500) NOT NULL,
`password` varchar(40) NOT NULL,
`slug` varchar(100) NOT NULL,
`mem_type` int(1) NOT NULL,
`pic` varchar(500) NOT NULL,
`paid` varchar(1) NOT NULL default '0',
`recur_date` date NOT NULL,
`recur_type` int(1) NOT NULL,
`description` text,
`website` varchar(250) default NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=utf8

 

CREATE TABLE `directory_item` (
`id` int(100) NOT NULL auto_increment,
`name` varchar(250) NOT NULL,
`contact` varchar(300) NOT NULL,
`email` varchar(300) NOT NULL,
`website` varchar(200) default NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=388 DEFAULT CHARSET=utf8

 

mysql_query = SELECT * FROM `directory_item` UNION SELECT * FROM `store`

  GROUP BY `name`

  LIMIT 0,15'

[envexlabs]

anyone?

 

This is a tad bit urgent.

 

Thanks.

[Barand]

SELECT id, name, contact_name as contact, email, website

FROM store

UNION

SELECT id, name, contact, email, website

FROM directory

ORDER BY name

[envexlabs]

hey,

 

so basically if it doesn't match both tables, i can't grab it?

 

That's good enough though, i will try this out.

 

Thanks!

[Barand]

You can but you need to select "dummy" data from the other table to preserve the number of columns

SELECT id, name, contact_name as contact, email, website, slug, recur_type
FROM store
UNION
SELECT id, name, contact, email, website, '', 0
FROM directory
ORDER BY name 

[fenway]

NULLs work too.