Merging rows
-
Hi everyone, First of all sorry for any mistake I might give as English it is not my natural language. My issues is: I built a query where I had to join 8 tables. The query should only be returning 4 rows but instead it is returning 8 rows as one of the tables I'm using in the join's has a field type to identify , for example if it is source and destination(0 or 1). The final result should return only one row with either with source and destination information base on the type. Two different columns (source,destination) but same row. Any ideas how to solve this? I appreciate any help. Many thanks.
-
Hi everyone, First of all sorry for any mistake I might give as English it is not my natural language. My issues is: I built a query where I had to join 8 tables. The query should only be returning 4 rows but instead it is returning 8 rows as one of the tables I'm using in the join's has a field type to identify , for example if it is source and destination(0 or 1). The final result should return only one row with either with source and destination information base on the type. Two different columns (source,destination) but same row. Any ideas how to solve this? I appreciate any help. Many thanks.
My English is bad and I'm English but my mind reading is atrocious. - Show the SQL
-
Hi everyone, First of all sorry for any mistake I might give as English it is not my natural language. My issues is: I built a query where I had to join 8 tables. The query should only be returning 4 rows but instead it is returning 8 rows as one of the tables I'm using in the join's has a field type to identify , for example if it is source and destination(0 or 1). The final result should return only one row with either with source and destination information base on the type. Two different columns (source,destination) but same row. Any ideas how to solve this? I appreciate any help. Many thanks.
Can you recreate the problem in a simple example on SQL Fiddle[^]? Without seeing your table structure, query, sample data, and expected output, it's almost impossible to answer your question.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
My English is bad and I'm English but my mind reading is atrocious. - Show the SQL
Hi Michael, Thanks for replying. Please find below a sample of the SQL. It is not the real script but reflects the scenario I tried to describe. "select shipId,number,reference,[weight],SA.typeOfAddress,addressLine as Source, addressLine as Destination FROM Shipments AS S INNER JOIN ShipmentAddresses AS SA ON SA.shipmentId = S.shipId INNER JOIN Addresses as A ON A.addressId = SA.addressId" This query is returning 2 rows: shipId | number | reference | weight | typeAddress| Source | Destination | 1 1 ref1 12 0 London Road London Road 1 1 ref1 12 1 LiverPool Lliverpool I would like a single row: shipId | number | reference | weight | Source | Destination | 1 1 ref1 12 London Road LiverPool Note: the type of address in the first example of results was just to indicate that 0 is source and 1 is destination. Many thanks for the help.
-
Can you recreate the problem in a simple example on SQL Fiddle[^]? Without seeing your table structure, query, sample data, and expected output, it's almost impossible to answer your question.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Hi Richard, Hope you are alright and thanks for replying. Could you please see my above answer to Michael? Any help will be appreciated. Many thanks.
-
Hi Michael, Thanks for replying. Please find below a sample of the SQL. It is not the real script but reflects the scenario I tried to describe. "select shipId,number,reference,[weight],SA.typeOfAddress,addressLine as Source, addressLine as Destination FROM Shipments AS S INNER JOIN ShipmentAddresses AS SA ON SA.shipmentId = S.shipId INNER JOIN Addresses as A ON A.addressId = SA.addressId" This query is returning 2 rows: shipId | number | reference | weight | typeAddress| Source | Destination | 1 1 ref1 12 0 London Road London Road 1 1 ref1 12 1 LiverPool Lliverpool I would like a single row: shipId | number | reference | weight | Source | Destination | 1 1 ref1 12 London Road LiverPool Note: the type of address in the first example of results was just to indicate that 0 is source and 1 is destination. Many thanks for the help.
Try something like this:
SELECT
S.shipId,
S.number,
S.reference,
S.[weight],
SA.addressLine As Source,
DA.addressLine As Destination
FROM
Shipments As S-- Source address: INNER JOIN ShipmentAddresses As SSA ON SSA.shipmentId = S.shipId And SSA.typeOfAddress = 0 INNER JOIN Addresses As SA ON SA.addressId = SSA.addressId -- Destination address INNER JOIN ShipmentAddresses As DSA ON DSA.shipmentId = S.shipId And DSA.typeOfAddress = 1 INNER JOIN Addresses As DA ON DA.addressId = DSA.addressId;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Try something like this:
SELECT
S.shipId,
S.number,
S.reference,
S.[weight],
SA.addressLine As Source,
DA.addressLine As Destination
FROM
Shipments As S-- Source address: INNER JOIN ShipmentAddresses As SSA ON SSA.shipmentId = S.shipId And SSA.typeOfAddress = 0 INNER JOIN Addresses As SA ON SA.addressId = SSA.addressId -- Destination address INNER JOIN ShipmentAddresses As DSA ON DSA.shipmentId = S.shipId And DSA.typeOfAddress = 1 INNER JOIN Addresses As DA ON DA.addressId = DSA.addressId;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Hi Richard, Sorry the delay in answering. the strategy you described above worked perfectly on my real production scenario. Many thanks.
