• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Help with SQL Statement

Shuxclams

Shuxclams

Diamond Member
I want to get the all "Market Numbers" (market_no) and all the "Store Numbers" (store_no) to show up with the "User Name" (username)

EXAMPLE:


Name: John Doe
Markets: 5,6,13,23,182
Store Numbers: 382,1813,1815,1816,1820,1821,1823

This is as close as I am getting
SELECT u1.username, ma.market_no, m1.store_no, m1.store_name, m1.regional_manager
FROM users AS u1, market_no AS ma, market_region_rep AS m1
WHERE ma.market_no=m1.market_no AND u1.user_id=m1.user_id
GROUP BY ma.market_no, m1.store_no
Click to expand...



AHA

SHUX
 
MySQL v 4.0.16

#----------------------------
# Table structure for market_region_rep
#----------------------------
CREATE TABLE `market_region_rep` (
`market_no` int(4) default NULL,
`store_no` int(4) NOT NULL default '0',
`store_name` varchar(64) default NULL,
`relo` varchar(32) default NULL,
`opened` date default NULL,
`regional_manager` varchar(32) default NULL,
`territory_manager` varchar(32) default NULL,
`user_id` int(11) default NULL,
`division` varchar(32) default NULL,
`region_id` int(11) default NULL,
`address` varchar(128) default NULL,
`city` varchar(32) default NULL,
`state` char(2) default NULL,
`zip_code` int(7) default NULL,
`phone_no` varchar(12) default NULL,
`fax_no` varchar(12) default NULL,
PRIMARY KEY (`store_no`),
KEY `market_no` (`market_no`),
CONSTRAINT `0_182` FOREIGN KEY (`market_no`) REFERENCES `market_no` (`market_no`)
) TYPE=InnoDB;
#----------------------------
# Records for table market_region_rep
#----------------------------


insert into market_region_rep values
(5, 349, 'Kenner', null, null, 'dave_mason', 'andy_silcox', 1, 'Southwest', 3, '1234 VETERANS BLVD', 'KENNER', 'LA', 00012, '0004649200', '0004645493'),
(5, 352, 'New Orleans East', null, null, 'dave_mason', 'andy_silcox', 1, 'Southwest', 3, '12300 I-10 SERVICE RD', 'NEW ORLEANS', 'LA', 70128, '0002464572', '0002442694');
Click to expand...

#----------------------------
# Table structure for users
#----------------------------
CREATE TABLE `users` (
`user_id` int(10) NOT NULL auto_increment,
`username` varchar(40) default NULL,
`first_name` varchar(40) default NULL,
`last_name` varchar(40) default NULL,
`title_type` int(11) unsigned NOT NULL default '0',
`title_name` varchar(50) NOT NULL default '',
`address` varchar(254) NOT NULL default '',
`city` varchar(36) NOT NULL default '',
`state` char(2) NOT NULL default '',
`zip_code` varchar(7) NOT NULL default '0',
`phone_no` varchar(12) default NULL,
`phone_no_ext` varchar(12) NOT NULL default '',
`mobile_phone_no` varchar(12) default NULL,
`password` varchar(50) default NULL,
`regdate` date NOT NULL default '0000-00-00',
`email_addr` varchar(56) NOT NULL default '',
`active` enum('Y','N') NOT NULL default 'Y',
`pager_no` varchar(12) default NULL,
`region_id` int(11) unsigned NOT NULL default '0',
`last_login` date default NULL,
`region_name` varchar(150) NOT NULL default '',
`add_email_addr` varchar(254) default NULL,
`fax_no` varchar(12) default NULL,
`group_ph_no` varchar(12) default NULL,
`hire_date` varchar(9) default '00-00-00',
`marital_status` varchar(11) default NULL,
`birth_day` varchar(9) default '00-00-00',
`unit_no` varchar(10) default NULL,
`pace_camera` varchar(14) NOT NULL default '',
`pace_modem` varchar(10) NOT NULL default '',
`pace_user` enum('N','Y') NOT NULL default 'N',
`pace_agreement` enum('Y','N') NOT NULL default 'N',
`pace_assign_date` date NOT NULL default '0000-00-00',
`blackberry_imei` varchar(16) default NULL,
`blackberry_pin` varchar(8) default NULL,
`blackberry_phone` varchar(12) default NULL,
`blackberry_model` varchar(4) default NULL,
`rim_agreement` enum('Y','N') NOT NULL default 'N',
`rim_assign_date` date NOT NULL default '0000-00-00',
`ss_no` varchar(12) default NULL,
`office_addr` varchar(64) default '1234 19th',
`office_city` varchar(64) default 'Santa Monica',
`office_state` varchar(64) default 'CA',
`office_zip` varchar(12) default '90404',
`office_ph` varchar(16) default '800-555-9922',
PRIMARY KEY (`user_id`),
KEY `username` (`username`),
KEY `region_id` (`region_id`),
KEY `title_type` (`title_type`),
KEY `title_name` (`title_name`),
KEY `region_name` (`region_name`),
KEY `rep_id` (`user_id`),
CONSTRAINT `0_23` FOREIGN KEY (`title_type`) REFERENCES `title` (`title_id`)
) TYPE=InnoDB;
#----------------------------
# Records for table users
#----------------------------



insert into users values
(1, 'Andrew_Silcox', 'Andrew', 'Silcox', 4, 'set', '70000 Blenhein Ridge', 'San Antonio', 'TX', '78231', '800-555-0971', '700', '800-555-2109', '5bf2f9c36a87cd696f2bdcfbd9847820', '0000-00-00', 'Andrew_Silcox@email.com', 'N', '', 2, '2003-12-16', 'SW', null, null, null, '0000-00-0', null, '0000-00-0', '', '', '', 'N', 'N', '0000-00-00', '010193000846122', '200DFB4C', '800-555-7617', '6230', 'N', '2004-06-22', null, '1234 19th', 'Santa Monica', 'CA', '90404', '800-555-9922'), (2, 'dave_mason', 'Dave', 'Mason', 1, 'corporate', '3210 Northhaven Road, #1', 'Dallas', 'TX', '75229', '800-555-9669', '701', '800-555-7645', '550e2169b9dcda489ddbc011d1dc4d64', '0000-00-00', 'dave_mason@email.com', 'Y', '', 3, '2003-12-29', 'SW', null, '800-555-9671', null, '0000-00-0', null, '0000-00-0', '', '', '', 'N', 'N', '0000-00-00', '010194000734284', '20074DF1', '800-555-9171', '7230', 'N', '2003-11-01', null, '1234 19th', 'Santa Monica', 'CA', '90404', '800-555-9922');
Click to expand...

#----------------------------
# Table structure for title
#----------------------------
CREATE TABLE `title` (
`title_id` int(11) unsigned NOT NULL auto_increment,
`title_name` varchar(50) NOT NULL default '',
PRIMARY KEY (`title_id`),
KEY `title_name` (`title_name`)
) TYPE=InnoDB;
#----------------------------
# Records for table title
#----------------------------


insert into title values
(1, 'corporate'),
(5, 'product_manager'),
(2, 'regional_manager'),
(3, 'service'),
(4, 'set');
Click to expand...

#----------------------------
# Table structure for market_no
#----------------------------
CREATE TABLE `market_no` (
`market_no` int(11) NOT NULL default '0',
PRIMARY KEY (`market_no`),
KEY `market_no` (`market_no`),
CONSTRAINT `0_185` FOREIGN KEY (`market_no`) REFERENCES `market_region_rep` (`market_no`)
) TYPE=InnoDB;
#----------------------------
# Records for table market_no
#----------------------------


insert into market_no values
(5),
(347),
(348),
(349),
(355),
(361),
(367);
Click to expand...


I think that will do it..... adjust accordingly.


One thing I was looking at was
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])

But it seems it is only usable with MySQL v. 4.1.x






SHUX
 
I guess I'm not really understanding what you're wanting. Are you wanting to see all store numbers and market numbers, and which users are assigned to each?
 
Originally posted by: KLin
I guess I'm not really understanding what you're wanting. Are you wanting to see all store numbers and market numbers, and which users are assigned to each?
Click to expand...



Exactly, I would like to see ;

+--------+-----------+-----------------------+
| username | market | Store Number |
+--------+-----------+-----------------------+
| andrew_silcox | 5 | 349,352 |
+--------+-----------+-----------------------+
Click to expand...



SHUX
 
I don't think you are going to be able to do that in a single sql call. You seem to want to return a variable number of rows for Store Number, but concatenate them all together into 1 field. Like in your example output, you'd need to get 2 rows:

andrew_silcox | 5 | 349
andrew_silcox | 5 | 352

You'd need to use a separate pl/sql script to build a concatenated store number list together.
 
Why would I want GROUP_CONCAT? It gives me what I am trying to do.

SELECT u1.username, ma.market_no, m1.store_no, m1.store_name, m1.regional_manager, GROUP_CONCAT(DISTINCT m1.store_no ORDER BY m1.store_no DESC SEPARATOR ',')
FROM users AS u1, market_no AS ma, market_region_rep AS m1
WHERE ma.market_no=m1.market_no AND u1.user_id=m1.user_id
GROUP BY ma.market_no, m1.store_no
Click to expand...

Would give me the exact result I am looking for...... but it is not avialable in MySQL v.4.0.x...




SHUX

 
Yeah, why would you want that? Are you going to do a copy and paste or something? Are you dumping the row right to the screen or something? Sounds kind of odd. Concatenated data isn't all that useful except for viewing directly.
 
Originally posted by: torpid
Yeah, why would you want that? Are you going to do a copy and paste or something? Are you dumping the row right to the screen or something? Sounds kind of odd. Concatenated data isn't all that useful except for viewing directly.
Click to expand...

Agreed. Data presentation should be handled by the presentation tier of your application, not by the SQL query.
 
use a loop to build variables that contain the markets and stores (variable for each) for each user? I can do this in VBA, but not PHP 😱
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top