Concatenating with Inner Join

[danny4444]

Hi there,

I am having trouble with making an inner join whilst concatenating two of the fields. I can do both separately but have not yet mastered both at the same time.

Here is what I have. It does work, however not in the intended way. It produces the only two results in my database but this is simply a coincidence because it is matching the actual string 'fullName' to '%a%' (I think).

SELECT * , DATE_FORMAT( `order_date` , '%d-%m-%Y' ) AS `date` , `fullName` FROM ( SELECT CONCAT( `customer_name` , ' ', `customer_surname` ) AS `fullName` , `customer_name` , `customer_surname` , `customer_id` , `customer_email` FROM `customer` ) AS `d` INNER JOIN `orders` ON `order_customer_id` = `customer_id` WHERE `fullName` LIKE '%a%' ORDER BY `customer_surname` ASC

Any help would be kindly appreciated.

Cheers

Danny

[smantscheff]

I don't understand what you're aiming at. Please provide some sample data and the desired result.

[danny4444]

Thanks for responding.

Right... well, I have a search field. When you type into this search field it connects to a (working) PHP script via an ajax connection (also working) that queries the database with the statement already posted. The results are displayed below the field.

I need to be able to search the order's table and customer table (joining them) by customer id, where the customer name (full name) is equal to what is typed in.

Does this clarify?

Cheers

Danny

[drjohn]

The concat does not need a second select.
also if you use Select *, it pulls up everything, but then you are asking it for more individually named fields, so several fields will appear twice!

so let's make a start on things.

Replace this

`fullName` FROM ( SELECT CONCAT( `customer_name` , ' ', `customer_surname` ) 
AS `fullName` ,

with this just this

CONCAT( `customer_name` , ' ', `customer_surname` ) 
AS `fullName` ,

But you seem to have other errors in the code. imho, so yes, post the tables and some sample data, so your over-complicated query can be re-written properly.

I can't really understand why you have in your sub query a call to concatenate customer_name with customer_surname, followed by a call for customer_name and customer_surname!

And whatever happens, it looks like you are trying to get all rows where someone has an 'a' anywhere in their first name or their surname - not exactly a useful query by any stretch of the imagination.

[drjohn]

The easiest way to tackle a problem is to reduce it to a much simpler problem, then expand it gradually.

Start by just running a join on the two tables, without the concatenation (which you could do in your PHP anyway) and with a simple LIKE '%a%', but listing every field you want returned in the query and see what you get. Then remove the customer_name and customer_surname, and replace them with the concat I gave above - you should get an identical result.

Then try instead of your current LIKE '%a%' using a name that was returned by the first query.

Eventually at some point you will have to make it use whatever variable is passed by the form, but if you can't get the simpler examples above to work, something else is going wrong.

[danny4444]

The 'a' is an example... as the user types it refines the search. It removes all that do not match 'a'... then you type 'n' ('an') this would remove all results not matching 'an' then before you know it you've typed 'andrew' and you have all results matching 'andrew'.

It's a live search of the database. But the results returned need to be from both the `customer` and `orders` table. The search needs to be of customer_name and customer_surname.

If I have mistakes in the logic it's a mix of lots of advice from various sites. Now I've hit a dead end and not sure what to try (hence the reason I am here).

Here is the data for `customer`

CREATE TABLE IF NOT EXISTS `customer` (
  `customer_id` int(9) NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(256) NOT NULL,
  `customer_surname` varchar(256) NOT NULL,
  `customer_email` varchar(350) NOT NULL,
  `customer_password` varchar(24) NOT NULL,
  `customer_telephone` int(15) NOT NULL,
  `customer_business` varchar(256) NOT NULL,
  `customer_addressline1` varchar(256) NOT NULL,
  `customer_addressline2` varchar(256) NOT NULL,
  `customer_city` varchar(256) NOT NULL,
  `customer_county` varchar(256) NOT NULL,
  `customer_postcode` varchar(15) NOT NULL,
  `customer_billing_addressline1` varchar(256) NOT NULL,
  `customer_billing_addressline2` varchar(256) NOT NULL,
  `customer_billing_city` varchar(256) NOT NULL,
  `customer_billing_county` varchar(256) NOT NULL,
  `customer_billing_postcode` varchar(15) NOT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;



INSERT INTO `customer` (`customer_id`, `customer_name`, `customer_surname`, `customer_email`, `customer_password`, `customer_telephone`, `customer_business`, `customer_addressline1`, `customer_addressline2`, `customer_city`, `customer_county`, `customer_postcode`, `customer_billing_addressline1`, `customer_billing_addressline2`, `customer_billing_city`, `customer_billing_county`, `customer_billing_postcode`) VALUES
(1, 'Joe', 'Bloggs', '[e-mail]', 'lala', 132568954, 'Joey''s Jewellery', '1 Necklace Road', 'Diamond Valley', 'Star City', 'Hampshire', 'ST12 3AB', '', '', '', '', ''),
(2, 'Jill', 'Jones', '[e-mail]', 'lala', 1234567890, 'Jilly Jones'' Jewels', '2 Ruby Road', 'Quartz Village', 'Crystal Town', 'Hampshire', 'CT12 3CD', '', '', '', '', ''),
(3, 'Thomas', 'Anderson', '[e-mail]', 'lala', 1234567890, 'Tommy''s Watches', '5 Quartz Road', '', 'Stone City', 'Hampshire', 'ST12 3EF', '5 Quartz Road', '', 'Stone City', 'Hampshire', 'ST12 3EF'),
(4, 'Andrew', 'Smith', '[e-mail]', 'lala', 1234567890, 'Andy''s Silver', '105 Emerald Lane', 'Green Way', 'Star City', 'Hampshire', 'ST12 0FE', '105 Emerald Lane', 'Green Way', 'Star City', 'Hampshire', 'ST12 0FE'),

And here is `orders`

CREATE TABLE IF NOT EXISTS `orders` (
  `order_id` int(9) NOT NULL,
  `order_customer_id` int(9) NOT NULL,
  `order_date` datetime NOT NULL,
  `order_pay_status` varchar(6) NOT NULL,
  `order_postage_status` varchar(6) NOT NULL,
  `order_discount_status` varchar(8) NOT NULL,
  `order_discount_amount` int(2) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `orders` (`order_id`, `order_customer_id`, `order_date`, `order_pay_status`, `order_postage_status`, `order_discount_status`, `order_discount_amount`) VALUES
(1401201101, 4, '2011-01-14 11:14:14', 'yes', 'yes', 'active', 5),
(2147483647, 3, '2011-01-12 11:15:23', 'yes', 'no', 'inactive', 0);

As you can see I wanted to avoid posting all of this if possible, as there's lots of it. Hopefully this is all you need to see what I mean.

Cheers

Danny

[danny4444]

Okay, I'll have another crack at it :)

Cheers.

Danny

[drjohn]

this works on your tables and sample data.

SELECT concat(customer_name,' ', customer_surname) as FullName, customer_id, customer_email
FROM customer INNER JOIN orders ON order_customer_id = customer_id 
WHERE concat(customer_name,' ', customer_surname) LIKE '%andr%'
ORDER By customer_surname ASC

It seems you can't use an alias in a conditional statement in MySQL. Hence the concat() is repeated as the condition.
Edit - actually it seems standard SQL doesn't allow aliases as part of a condition either, not just MySQL. It's because the where may be evaluated before the alias is applied, so it gets confused in the relational algebra and can't do it's thing as no such column exists. This suggests that the alias is probably only applied at the final output stage of the query.

Notice the total lack of a sub query.

If you're happy, visit one of my websites.

PS A better set of test data would have had a third row in the orders table, placed by Joe or Jill, so you could check that it wasn't returned when not needed.

[danny4444]

Hey cheers, I think that has solved it! And better yet I understand! lol.

Happy days.

Looked at both sites ;)

Thanks loads for your help. Much Appreciated.

Danny