select distinct field from multiple tables

[webent]

MySQL version 4.1.22-standard-log

 

Hi, I need to get all the distinct/unique manufacturer's names from all the tables... I was wondering if anyone could tell me why this query isn't giving me the desired results,... from the research I'm doing on the subject, it should work... I think?! I have tried so many variations, this one really seemed like the one... but the results end up with MANUFACTURERS_NAME as several columns and a few of the manufacturers repeated on the left, but all the other MANUFACTURERS_NAME columns just repeat the same manufactuer over that isn't present in the left column... And beg for mercy if you try the query without a limit at the end, I thought I was going to have to "Try" a reboot of the server...

 

SELECT DISTINCT 
a.MANUFACTURERS_NAME, 
b.MANUFACTURERS_NAME,
c.MANUFACTURERS_NAME,
d.MANUFACTURERS_NAME,
e.MANUFACTURERS_NAME,
f.MANUFACTURERS_NAME,
g.MANUFACTURERS_NAME,
h.MANUFACTURERS_NAME,
i.MANUFACTURERS_NAME,
j.MANUFACTURERS_NAME,
k.MANUFACTURERS_NAME
FROM 
webwired_dropshipdirect.apparel AS a, 
webwired_dropshipdirect.arts_crafts AS b,
webwired_dropshipdirect.at_home AS c,
webwired_dropshipdirect.automotive AS d,
webwired_dropshipdirect.b2b_services AS e,
webwired_dropshipdirect.collectibles AS f,
webwired_dropshipdirect.electronics AS g,
webwired_dropshipdirect.gift_items AS h,
webwired_dropshipdirect.jewelry AS i,
webwired_dropshipdirect.professional AS j,
webwired_dropshipdirect.sport_outdoor AS k  
LIMIT 0 , 30

[fenway]

What output to do you want?  You're currently joining 11 tables without a join condition... which makes me think you want a UNION.

[webent]

I just want the "DISTINCT MANUFACTURERS_NAME" from all 11 tables... I tried a UNION like so...

 

SELECT DISTINCT MANUFACTURERS_NAME FROM
(
  SELECT MANUFACTURERS_NAME.apparel as a 
  FROM webwired_dropshipdirect.apparel   
UNION
  SELECT MANUFACTURERS_NAME.arts_crafts as b 
  FROM webwired_dropshipdirect.arts_crafts 
etc...
)

 

but that didn't work out, as I'm sure you can tell...

 

Edit: From what I could tell about JOIN, you can't join more than one table, every example I came across only showed two tables in total being joined together...

[webent]

I tried this, but got the exact same result as the first one I posted...

 

SELECT DISTINCT 
apparel.MANUFACTURERS_NAME, 
arts_crafts.MANUFACTURERS_NAME, 
at_home.MANUFACTURERS_NAME,
automotive.MANUFACTURERS_NAME, 
b2b_services.MANUFACTURERS_NAME, 
collectibles.MANUFACTURERS_NAME,
electronics.MANUFACTURERS_NAME, 
gift_items.MANUFACTURERS_NAME, 
jewelry.MANUFACTURERS_NAME,
professional.MANUFACTURERS_NAME, 
sport_outdoor.MANUFACTURERS_NAME
FROM 
webwired_dropshipdirect.apparel, 
webwired_dropshipdirect.arts_crafts,
webwired_dropshipdirect.at_home,
webwired_dropshipdirect.automotive,
webwired_dropshipdirect.b2b_services,
webwired_dropshipdirect.collectibles,
webwired_dropshipdirect.electronics,
webwired_dropshipdirect.gift_items,
webwired_dropshipdirect.jewelry,
webwired_dropshipdirect.professional,
webwired_dropshipdirect.sport_outdoor                                     
LIMIT 0 , 30

 

There's got to be a way to like create an alias for all the tables.MANUFACTURERS_NAME, like...

 

SELECT DISTINCT 
(apparel.MANUFACTURERS_NAME, 
arts_crafts.MANUFACTURERS_NAME, 
at_home.MANUFACTURERS_NAME,
automotive.MANUFACTURERS_NAME, 
b2b_services.MANUFACTURERS_NAME, 
collectibles.MANUFACTURERS_NAME,
electronics.MANUFACTURERS_NAME, 
gift_items.MANUFACTURERS_NAME, 
jewelry.MANUFACTURERS_NAME,
professional.MANUFACTURERS_NAME, 
sport_outdoor.MANUFACTURERS_NAME) as DIST_MAN
FROM 
webwired_dropshipdirect.apparel, 
webwired_dropshipdirect.arts_crafts,
webwired_dropshipdirect.at_home,
webwired_dropshipdirect.automotive,
webwired_dropshipdirect.b2b_services,
webwired_dropshipdirect.collectibles,
webwired_dropshipdirect.electronics,
webwired_dropshipdirect.gift_items,
webwired_dropshipdirect.jewelry,
webwired_dropshipdirect.professional,
webwired_dropshipdirect.sport_outdoor                                     
LIMIT 0 , 30

 

Of course that wouldn't work, LOL,... I know, cause I tried it...

[fenway]

You want:

 

SELECT DISTINCT MANUFACTURERS_NAME FROM

(

  SELECT MANUFACTURERS_NAME  FROM webwired_dropshipdirect.apparel 

UNION

  SELECT MANUFACTURERS_NAME  FROM webwired_dropshipdirect.arts_crafts

etc...

)