join query

[corruptcity || skalman]

hi im having a problem with trying to make my drugs mod ive got a page called drugstreets and on that page ive got a table that needs to get info from 2 tables 1 table holds the drugs and what they are worth and the other table holds all the drugs that that user has. here is my problem im trying to join the 2 tables but what ive tried it doesnt work as i dont have anythink that needs to be linked between then 2. so what would be the best way to fix or sort this is out

if you need any more info or want to see the code sofar just holla

[fbiss]

can you export the table structures for the two tables and add them here?

[corruptcity || skalman]

yeah sure there you go fbiss and thanks again

[mysql]

-- Table structure for table `drugs_streets`

--

CREATE TABLE IF NOT EXISTS `drugs_streets` (

`streetid` int(11) NOT NULL AUTO_INCREMENT,

`location` int(11) NOT NULL,

`drug` text NOT NULL,

`defaultprice` int(11) NOT NULL,

`buy` int(11) NOT NULL,

`sell` int(11) NOT NULL,

PRIMARY KEY (`streetid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=67 ;

--

[/mysql]

[mysql]

-- Table structure for table `drugs_drugs`

--

CREATE TABLE IF NOT EXISTS `drugs_drugs` (

`drugid` int(11) NOT NULL AUTO_INCREMENT,

`userid` int(11) NOT NULL,

`crack` int(11) NOT NULL,

`cocaine` int(11) NOT NULL,

`heroin` int(11) NOT NULL,

`weed` int(11) NOT NULL,

`mdma` int(11) NOT NULL,

`lsd` int(11) NOT NULL,

`speed` int(11) NOT NULL,

`ketamine` int(11) NOT NULL,

`speck` int(11) NOT NULL,

`crystalmeth` int(11) NOT NULL,

`amphetamine` int(11) NOT NULL,

PRIMARY KEY (`drugid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--

[/mysql]

[fbiss]

I think you need to reconsider your table structures. unless someone else chimes in, I dont know of any effecient way to left join a table to multiple columns like that.

If i were you, i would rebuild your code setup to something like these 3 tables. This way it will more like the item/inventory tables.

[mysql]-- Table structure for table `drugs_streets`

--

CREATE TABLE IF NOT EXISTS `drugs_streets` (

`streetid` int(11) NOT NULL AUTO_INCREMENT,

`location` int(11) NOT NULL,

`drugid` int(11) NOT NULL,

`defaultprice` int(11) NOT NULL,

`buy` int(11) NOT NULL,

`sell` int(11) NOT NULL,

PRIMARY KEY (`streetid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=67 ;

--[/mysql]

I have changed `drug` to `drugid` in this table and made it an int, you will relate the number here to match the `drugid` in table drugs_drugs

----------------------------------------------------

[mysql]-- Table structure for table `drugs_drugs`

--

CREATE TABLE IF NOT EXISTS `drugs_drugs` (

`drugid` int(11) NOT NULL AUTO_INCREMENT,

`drugtype` varchar(255) NOT NULL default '',

PRIMARY KEY (`drugid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--[/mysql]

add a row for each type of drug (cocaine, heroin, weed, etc)

----------------------------------------------------

[mysql]-- Table structure for table `drugs_inv`

--

CREATE TABLE IF NOT EXISTS `drugs_inv` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`userid` int(11) NOT NULL,

`drugid` int(11) NOT NULL,

`qty` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--[/mysql]

[corruptcity || skalman]

ok tyvm for your input fbiss