SQL combining multiple columns from same table while querying multiple tables

Question

I have got three tables that looks as follows.

usedetails [ID,first_name,last_name,telephone,email]
address [ID,streetnumber,streetname,town,county,postcode,userdetailsID]
BOOKING [ID,customerID,pickup_address_id,dropoff_address_id,charge,no_of_passenger]

Address table holds two types of address ie pickoff and dropoff. I would like to display each of the two addresses as one string. The following is my query.

query = "SELECT A.streetnumber,
                        A.streetname,
                        A.town,
                        A.postcode
                    AS pickup_point
                        AB.streetnumber,
                        AB.streetname,
                        AB.town,
                        AB.postcode
                    AS  dropoff_point
                        UD.first_name,
                        UD.last_name,
                        UD.telephone,
                        UD.email
                    FROM userdetails UD 
                    INNER JOIN booking B
                    ON B.customerID = UD.ID
                    INNER JOIN address A
                    ON B.pickup_address_id = A.ID
                    INNER JOIN address AB
                    ON AB.drop_off_address_id = A.ID
                    WHERE UD.ID = A.userdetailsID OR UD.ID = AB.userdetailsID";

Vladislav Tesařík · Accepted Answer · 2015-03-10 14:15:29Z

2

Try CONCAT function:

SELECT CONCAT(A.streetnumber,
              ' ',
              A.streetname,
              ' ',
              A.town,
              ' ',
              A.postcode) AS pickup_point, ...

Or CONCAT_WS function to pass separator as the first argument:

SELECT CONCAT_WS(' ',
                 A.streetnumber,
                 A.streetname,
                 A.town,
                 A.postcode) AS pickup_point, ...

edited Mar 10, 2015 at 14:15

answered Mar 10, 2015 at 14:09

Vladislav Tesařík

1132 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3034845 Over a year ago

Thank a lot it worked, however my query works only from phpMyAdmin but not from the HTML page. Any possible reason?

Vladislav Tesařík Over a year ago

What's the output of your HTML page?

user3034845 Over a year ago

I was missing some opening and closing tags to a variable. Thank you very much. Its now working.

Collectives™ on Stack Overflow

SQL combining multiple columns from same table while querying multiple tables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related