asked on

php mysql query

Hi, I am not sure if mysql can do this or not but figured I would ask:

1. I have a table with a column with this data point:
bunk = BA3-01B

2. I have a table as boats
column abbreviation = BA3

3. I have a table as bunks
boatID = 1
cabin = 01
bunk = B

Now in my inventory table I have a column called bunks with "BA3-01B"

I want to take "BA3-01B" and search "BA3" on table boats to get the boatID then search boatID, cabin "01" and bunk "B" from table bunks and return table bunks - cabin_type data.

Here is the query I was messing with. It did not error but did not return any results. I think I am on the right track.

SELECT
	`charters`.`boatID`,
	`boats`.`abbreviation`,
	`bunks`.`cabin_type`


FROM
	`inventory`,`charters`,`boats`,`bunks`

WHERE
	`inventory`.`inventoryID` = '9150099'
	AND `inventory`.`charterID` = `charters`.`charterID`
	AND `charters`.`boatID` = `boats`.`boatID`
	AND `boats`.`boatID` = `bunks`.`boatID`
	AND `inventory`.`bunk` = CONCAT(`boats`.`abbreviation` + '-' + `bunks`.`cabin`,`bunks`.`bunk`)

Open in new window

Robert Saylor

ASKER

ok, I solved my own question :)

I created a temp table and inserted the char "-" then included that in my concat and the query returned the data I wanted.

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Saylor

ASKER

No but I will. I was using the plus like in JavaScript. I bet that would do it as well.

Thanks!