MYSQL concat() function

Question

0.00/5 (No votes)

See more:

Hi,

I have 2 Tables as buss and line containing data as :

SQL

buss (busSrno, BusName). busSrno is PK.
1  bus1
2  bus2
3  bus3

line (lineNo, Frombus, Tobus) : here Frombus and Tobus columns will contain values of busSrno from buss table. lineNo is PK.
1  1  2
2  1  3
3  2  3
4  3  2

Now I have the following Query :

SQL

select concat(
       (select BusName as 'frombusName' from buss where busSrno in    (select frombus from line)), '-',
       (select BusName as 'tobusName' from buss where busSrno in 
(select tobus from line))
             ) as LineName;

It shows Following ERROR as : Sub-query returns more than 1 row.
Expected o/p : Under LineName column it will show as :

SQL

LineName

  bus1-bus2
  bus1-bus3
  bus2-bus3
  bus3-bus2

Kindly let me know the solution.

Thanks n Regards,

Posted 5-Jan-11 0:24am

shwetavc30

Updated 5-Jan-11 0:41am

TweakBird

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

yaprig · Answer 1 · 2011-01-05T00:52:00

Solution 1

refer this..might be helpful

http://dbaspot.com/forums/sqlserver-programming/188878-concatenate-values-two-tables-one-using-count.html[^]

http://www.questionhub.com/StackOverflow/4104922[^]

Posted 5-Jan-11 0:52am

yaprig

Comments

shwetavc30 5-Jan-11 7:40am

Hi,

Thanks for ur help..
I got the Solution by Re-Writing Query as :-
select concat(b1.busname, '---', b2.busname) as 'LineName' from buss b1, buss b2, line
where b1.busSrno = line.FromBus and b2.busSrno = line.ToBus;

Thanks n Regards,

yaprig 6-Jan-11 0:31am

:)

Hiren solanki · Answer 2 · 2011-01-05T01:01:00

Solution 2

You have not completely understood idea behind purpose of IN query.

It is giving error because there are corrosponding many frombus in line table for corrosponding busSrno in buss.

further more concat function only need Value-joiner-Value type syntax to join but there is returning multiple value inspace of single value so that it will cause error.

Posted 5-Jan-11 1:01am

Hiren solanki

Comments

shwetavc30 5-Jan-11 7:20am

Kindly let me know another solution then for the mentioned expected result. I can do the same by writing stored procedure. But I wish to do this in a single query. Please let me know whether it is possible.

shwetavc30 5-Jan-11 7:39am

Hi Hiren,

Thanks for ur help..
I got the Solution by Re-Writing Query as :-
select concat(b1.busname, '---', b2.busname) as 'LineName' from buss b1, buss b2, line
where b1.busSrno = line.FromBus and b2.busSrno = line.ToBus;

Thanks n Regards,

Hiren solanki 5-Jan-11 7:40am

glad it helped you.

shwetavc30 · Answer 3 · 2011-01-05T01:38:00

Solution 3

Hi all,

Thanks for ur help..
I got the Solution by Re-Writing Query as :-
select concat(b1.busname, '---', b2.busname) as 'LineName' from buss b1, buss b2, line
where b1.busSrno = line.FromBus and b2.busSrno = line.ToBus;

Thanks n Regards,

Posted 5-Jan-11 1:38am

shwetavc30