Select statement question

[jay0316]

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?

 

[jay0316]

The MYSQL Version Number is 5.0.51a .

[fenway]

How about show us the actual sql statement... and tables?

[artacus]

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

 

[jay0316]

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;

 

[jay0316]

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.

[jay0316]

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