webent Posted July 3, 2008 Share Posted July 3, 2008 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 Link to comment https://forums.phpfreaks.com/topic/113100-select-distinct-field-from-multiple-tables/ Share on other sites More sharing options...
fenway Posted July 3, 2008 Share Posted July 3, 2008 What output to do you want? You're currently joining 11 tables without a join condition... which makes me think you want a UNION. Link to comment https://forums.phpfreaks.com/topic/113100-select-distinct-field-from-multiple-tables/#findComment-580980 Share on other sites More sharing options...
webent Posted July 3, 2008 Author Share Posted July 3, 2008 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... Link to comment https://forums.phpfreaks.com/topic/113100-select-distinct-field-from-multiple-tables/#findComment-580984 Share on other sites More sharing options...
webent Posted July 3, 2008 Author Share Posted July 3, 2008 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... Link to comment https://forums.phpfreaks.com/topic/113100-select-distinct-field-from-multiple-tables/#findComment-581251 Share on other sites More sharing options...
fenway Posted July 4, 2008 Share Posted July 4, 2008 You want: SELECT DISTINCT MANUFACTURERS_NAME FROM ( SELECT MANUFACTURERS_NAME FROM webwired_dropshipdirect.apparel UNION SELECT MANUFACTURERS_NAME FROM webwired_dropshipdirect.arts_crafts etc... ) Link to comment https://forums.phpfreaks.com/topic/113100-select-distinct-field-from-multiple-tables/#findComment-581447 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.