Jump to content

Select statement question


jay0316

Recommended Posts

I've got a list of stores that need to be dumped into a spreadsheet using a php script.  The trouble is that the php code that I found for creating a spreadsheet only excepts one select statement and I don't know how to write this in one statement. 

 

Some of the stores belong to a chain.  So, there is a corporate store and then children that fall under it.  Then there are also stores that are not part of a chain that need to be on the sheet.  I'd like to make sure that each time a chain is listed the corporate store is first followed by it's children.  All the stores will be listed alphabetically by name.

 

table

dealers - contains the store record

 

columns

Dealer_ID, Dealer_BusinessName - there is also a Dealer_Chain column that has "Corporate" or "Child" or "NULL"

 

table

dealer_chain_lookup -contains the Dealer_ID of the corporate and child store

 

columns

Corporate, Child

 

It looks like this

$result = @mysql_query($sql,$Connect)

is what the script is using to pull the data for the excel document.  Maybe if you don't know how to make the "select" in one  statement, you may know of a way to do it in two and compile the results in the $results variable?

 

Link to comment
Share on other sites

You'll need to do a left join:

 

SELECT chn.name AS chain, dlr.name AS dealer, ....
FROM dealers dlr
LEFT JOIN dealer_chain_lookup cl ON dlr.dealer_id = cl.dealer_id
LEFT JOIN dealer chn ON cl.chain_dealer_id = chn.dealer_id
ORDER BY 1, 2

 

Link to comment
Share on other sites

Thanks for the suggestion artacus.

 

I modified your statement to try and make it work.  So far, the corporate store is being displayed 16 times.  One time for every time I put a child store in I assume because each child id in the lookup table has a corporate id attached to it. 

 

Is there something I need to adjust in my statement?

 

SELECT *
FROM dealers AS dlr
LEFT JOIN dealers_chain_lookup AS cl ON dlr.Dealer_ID = cl.Corporate
LEFT JOIN dealers AS chn ON cl.Child = chn.Dealer_ID
ORDER BY 1, 2;

 

Link to comment
Share on other sites

Ok.  I think I fixed the multiple corporate listings by changing the select to:

 

SELECT *
FROM dealers AS dlr
LEFT JOIN dealers_chain_lookup AS cl ON dlr.Dealer_ID = cl.Corporate
LEFT JOIN dealers AS chn ON cl.Child = chn.Dealer_ID
GROUP BY dlr.Dealer_ID
ORDER BY 1,2;

 

However, I'm having an issue with the corporate store not appearing above the child store.

Link to comment
Share on other sites

here are the 2 tables:

dealers

CREATE TABLE `dealers` (\n  `Dealer_ID` mediumint(11) unsigned zerofill NOT NULL auto_increment,\n  ``Dealer_Zip` varchar(255) character set latin1 default NULL,\n  `Account_Number` varchar(11) character set latin1 NOT NULL,\n  `Dealer_Chain` varchar(10) character set latin1 default NULL,\n  `AS400_BusinessName` varchar(255) NOT NULL,\n  `Dealer_BusinessName` varchar(255) character set latin1 default NULL,\n  `Dealer_Username` varchar(16) character set latin1 default NULL,\n  `Dealer_Password` varchar(16) character set latin1 default NULL,\n  `Dealer_Address1` varchar(255) character set latin1 default NULL,\n  `Dealer_Address2` varchar(50) character set latin1 default NULL,\n  `Dealer_City` varchar(255) character set latin1 default NULL,\n  `Dealer_State` varchar(255) character set latin1 default NULL,\n  `Dealer_Country` varchar(50) character set latin1 default NULL,\n  `Dealer_Phone` varchar(255) character set latin1 default NULL,\n    PRIMARY KEY  (`Dealer_ID`),\n  UNIQUE KEY `ID_2` (`Dealer_ID`),\n  KEY `ID` (`Dealer_ID`)\n) ENGINE=MyISAM AUTO_INCREMENT=1195 DEFAULT CHARSET=ascii

 

dealers_chain_lookup

CREATE TABLE `dealers_chain_lookup` (\n  `ID` int(5) NOT NULL auto_increment,\n  `Corporate` mediumint(11) NOT NULL,\n  `Child` mediumint(11) NOT NULL,\n  PRIMARY KEY  (`ID`)\n) ENGINE=MyISAM AUTO_INCREMENT=60 DEFAULT CHARSET=latin1

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.