SQL query shows error with more than one join in one table
I have searched on StackOverflow and on the web but could not find a solution to this problem. I have a table myTable type in SQL Server 2008 R2 with these columns:
person_id
, city
, addressType
, line_num
, textarea
and some other columns, but they are not needed. All fields are int, but textarea is varchar (10). A person can have several addresses in a city. The address can be large, so each type of address can have multiple line numbers. Address type 1 is the home address, address type 2 is the office address, and address 3 is another address. Line examples are given here
person_id|city|addressType|line_num|textarea
1 |1 |1 |0 |House no.
---------------------------------------------
1 |1 |1 |1 |10
---------------------------------------------
1 |1 |2 |0 |Building5
---------------------------------------------
1 |1 |2 |1 |Floor 1
---------------------------------------------
1 |1 |3 |0 |Factory
---------------------------------------------
1 |1 |3 |1 |no. 30
I need to show all types of addresses for a person in one line. Like this:
person_id|city|homeAddress |officeAddress |otherAddress
1 |1 |House no.,10|Building5,Floor 1|Factory,no.30
I used a connection. Join works well for type 1 address and type of address.
Here is a single connection query that works fine:
SET ANSI_PADDING ON;
SELECT DISTINCT homeAddress_person_id, homeAddress_city, homeAddress, officeAddress
FROM
/*Sub query for home address*/
(SELECT person_id AS homeAddress_person_id, city AS homeAddress_city,
/*Concatinating home address*/
STUFF((SELECT ',' + textarea FROM myTable WHERE person_id=ResultsFrom_myTable_For_homeAddress.person_id AND city=ResultsFrom_myTable_For_homeAddress.city AND addressType=ResultsFrom_myTable_For_homeAddress.addressType FOR XML PATH(''),TYPE).value('.','VARCHAR(4000)'),1,1,'') AS homeAddress
FROM myTable ResultsFrom_myTable_For_homeAddress WHERE person_id=1 AND addressType=1
GROUP BY person_id,city,addressType) ResultsFrom_myTable_For_homeAddress_outer
FULL OUTER JOIN
/*Sub query for office address*/
(SELECT person_id AS officeAddress_person_id, city AS officeAddress_city,
/*Concatinating office address*/
STUFF((SELECT ',' + textarea FROM myTable WHERE person_id=ResultsFrom_myTable_For_officeAddress.person_id AND city=ResultsFrom_myTable_For_officeAddress.city AND addressType=ResultsFrom_myTable_For_officeAddress.addressType FOR XML PATH(''),TYPE).value('.','VARCHAR(4000)')
,1
,1
,'') AS officeAddress
FROM myTable ResultsFrom_myTable_For_officeAddress
WHERE person_id=1 AND addressType=2
GROUP BY person_id,city,addressType) ResultsFrom_myTable_For_officeAddress_outer
ON ResultsFrom_myTable_For_homeAddress_outer.homeAddress_person_id=ResultsFrom_myTable_For_officeAddress_outer.officeAddress_person_id AND ResultsFrom_myTable_For_homeAddress_outer.homeAddress_city=ResultsFrom_myTable_For_officeAddress_outer.officeAddress_city
SET ANSI_PADDING OFF;
But when I add another connection to show a different address. It shows an error Non-boolean expression specified in the context where the condition is expected Here is a query with two joins
SET ANSI_PADDING ON;
SELECT DISTINCT homeAddress_person_id, homeAddress_city, homeAddress, officeAddress, otherAddress
FROM
/*Sub query for home address*/
(SELECT person_id AS homeAddress_person_id, city AS homeAddress_city,
/*Concatinating home address*/
STUFF((SELECT ',' + textarea FROM myTable WHERE person_id=ResultsFrom_myTable_For_homeAddress.person_id AND city=ResultsFrom_myTable_For_homeAddress.city AND addressType=ResultsFrom_myTable_For_homeAddress.addressType FOR XML PATH(''),TYPE).value('.','VARCHAR(4000)'),1,1,'') AS homeAddress
FROM myTable ResultsFrom_myTable_For_homeAddress WHERE person_id=1 AND addressType=1
GROUP BY person_id,city,addressType) ResultsFrom_myTable_For_homeAddress_outer
FULL OUTER JOIN
/*Sub query for office address*/
(SELECT person_id AS officeAddress_person_id, city AS officeAddress_city,
/*Concatinating office address*/
STUFF((SELECT ',' + textarea FROM myTable WHERE person_id=ResultsFrom_myTable_For_officeAddress.person_id AND city=ResultsFrom_myTable_For_officeAddress.city AND addressType=ResultsFrom_myTable_For_officeAddress.addressType FOR XML PATH(''),TYPE).value('.','VARCHAR(4000)')
,1
,1
,'') AS officeAddress
FROM myTable ResultsFrom_myTable_For_officeAddress
WHERE person_id=1 AND addressType=2
GROUP BY person_id,city,addressType) ResultsFrom_myTable_For_officeAddress_outer
ON ResultsFrom_myTable_For_homeAddress_outer.homeAddress_person_id=ResultsFrom_myTable_For_officeAddress_outer.officeAddress_person_id AND ResultsFrom_myTable_For_homeAddress_outer.homeAddress_city=ResultsFrom_myTable_For_officeAddress_outer.officeAddress_city
FULL OUTER JOIN
/*Sub query for other address*/
(SELECT person_id AS otherAddress_person_id,city AS otherAddress_city,
/*Concatinating office address*/
STUFF((SELECT ',' + textarea FROM myTable WHERE person_id=ResultsFrom_myTable_For_otherAddress.person_id AND city=ResultsFrom_myTable_For_otherAddress.city AND addressType=ResultsFrom_myTable_For_otherAddress.addressType FOR XML PATH(''),TYPE).value('.','VARCHAR(4000)'),1,1,'') AS otherAddress
FROM myTable ResultsFrom_myTable_For_otherAddress WHERE person_id=1 AND addressType=3
GROUP BY person_id,city,addressType) ResultsFrom_myTable_For_otherAddress_outer
ON ResultsFrom_myTable_For_homeAddress_outer.homeAddress_person_id=ResultsFrom_myTable_For_otherAddress_outer.otherAddress_person_id AND ResultsFrom_myTable_For_homeAddress_outer.homeAddress_city=ResultsFrom_myTable_For_otherAddress_outer.otherAddress_city;
SET ANSI_PADDING OFF;
What's wrong with the above query?
source to share
using common table expression and conditional aggregation, we can clean up this query and include it in this:
with cte as (
select
person_id
, city
, addressType
, address = stuff((
select ',' + i.textarea
from myTable i
where i.person_id = t.person_id
and i.city = t.city
and i.addressType = t.addressType
order by line_num
for xml path(''), type).value('.', 'varchar(4000)'), 1, 1, '')
from MyTable as t
group by person_id, city, addressType
)
select
t.person_id
, t.city
, homeAddress = max(case when t.addressType = 1 then t.address end)
, officeAddress = max(case when t.addressType = 2 then t.address end)
, otherAddress = max(case when t.addressType = 3 then t.address end)
from cte as t
where t.person_id = 1
group by t.person_id, t.city
registry: http://rextester.com/RWIQ34896
returns:
+-----------+------+--------------+-------------------+----------------+
| person_id | city | homeAddress | officeAddress | otherAddress |
+-----------+------+--------------+-------------------+----------------+
| 1 | 1 | House no.,10 | Building5,Floor 1 | Factory,no. 30 |
+-----------+------+--------------+-------------------+----------------+
Your question says addressType
0 is home, but your request uses addressType
1. I believe you can make the correct settings for this request anyway.
source to share