April 4, 2011 at 11:15 am
Dear Experts,
I have 3 different tables that I would like to query data from and put it in the following format
ConsignmentValue Duedate CardCode Farm1 Farm2 Farm3
325 01/05/2011 C200006 500 200 300
340 01/05/2011 C200007 100
342 01/05/2011 C200032 50 100
The tables all have similar data but the column ConsignmentValue can be found in all 3 tables and it is used as the connector. Such that from Consignment Value 325 I should dislplay the Credit Data from the 3 Farms in the the layout above.
The problem I face is that where the consignment value has been repeated in the tables more than once where the duedate is diffrent I get replications which mess up my query, due to the large number of rows in the tables the query fails to execute.
Kindly Find the DDLs below.
CREATE TABLE #mytable1
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DateValue DATETIME,
MyValue DECIMAL(9,4),
Credit INT,
CARDCODE VARCHAR (20),
ConsigmentValue VARCHAR (20),
Farm1 Varchar (20),
)
CREATE TABLE #mytable2
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DateValue DATETIME,
MyValue DECIMAL(9,4),
Credit INT,
CARDCODE VARCHAR (20) INT,
ConsigmentValue VARCHAR (20),
Farm1 Varchar (20),
)
CREATE TABLE #mytable2
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DateValue DATETIME,
MyValue DECIMAL(9,4),
Credit INT,
CARDCODE VARCHAR (20) INT,
ConsigmentValue VARCHAR (20),
Farm1 Varchar (20),
)
// This should populate all 3 tables as the data held is similar.
SELECT '5766','May 1 2011 12:00AM','11206.800000','C200006','325','Farm1' UNION ALL
SELECT '5767','May 1 2011 12:00AM','11206.800000','C200007','325','Farm2' UNION ALL
SELECT '5768','May 1 2011 12:00AM','2460.760000','C200032','340','Farm3' UNION ALL
SELECT '5769','May 1 2011 12:00AM','2460.760000','C200118','342','Farm1' UNION ALL
SELECT '5770','May 1 2011 12:00AM','23696.240000','C200038','339','Farm2' UNION ALL
SELECT '5771','May 1 2011 12:00AM','35316.540000','C200033','338','Farm3' UNION ALL
SELECT '5772','May 1 2011 12:00AM','3873.430000','C200045','51','Farm3' UNION ALL
SELECT '5773','May 1 2011 12:00AM','6835.460000','C200117','51','Farm2'
Please help get solution for this.
April 4, 2011 at 11:30 am
Can you not join all three tables on the ConsignmentValue field and just display the date?
select a.date1, b.date2, c.date3
from a
inner join b
on a.ConsignmentValue = b.ConsignmentValue
inner join c
on a.ConsignmentValue = c.ConsignmentValue
April 4, 2011 at 12:10 pm
I agree with Steve. I would just join on consignmentvalue. Is that field always a numeric? If so, I would change it to a different datatype than varchar.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2011 at 12:03 am
Hi Steve,
For rows that don't have the consignment number using the INNER JOIN will leave them out. How can I get around that ?
April 5, 2011 at 4:51 am
Hi Steve,
I still get replication when I do this. Is there a way I could control this ?
April 5, 2011 at 5:27 am
martin.edward (4/5/2011)
Hi Steve,I still get replication when I do this. Is there a way I could control this ?
When you say replication, do you mean repeated rows?
You are getting this as a result set:
ConsignmentValue Duedate CardCode Farm1 Farm2 Farm3
325 01/05/2011 C200006 500 200 300
340 01/10/2011 C200007 100
342 01/25/2011 C200032 50 100
340 01/05/2011 C200007 100
342 01/05/2011 C200032 50 100
When you are looking for this:
ConsignmentValue Duedate CardCode Farm1 Farm2 Farm3
325 01/05/2011 C200006 500 200 300
340 01/05/2011 C200007 100
342 01/05/2011 C200032 50 100
If I guessed correctly, you have a few choices. Are you looking for the greatest date? Using two rows from the above example:
340 01/10/2011 C200007 100
340 01/05/2011 C200007 100
If you want the row with the date 01/01/2011, then a multiple subqueries should work.
SELECT T1.ConsigmentValue, T1.DateValue, T1.CardCode, T1.Farm1
FROM Table1 as T1
INNER JOIN (SELECT MaxValue.ConsigmentValue, MAX(MaxValue.DateValue) as MaxDateValue
FROM Table1 as MaxValue
GROUP BY MaxValue.ConsigmentValue) as MaxValue ON T1.ConsigmentValue = MaxValue.ConsigmentValue
AND T1.DateValue = MaxValue.DateValue
The cautions with this approach would be if the dates also repeat. You will get multiple rows. In that case, if you can use the most recently extered values, using MAX on the identity fields may also work. Replace the DateVaue with the ID field.
You also have NULLS to deal with, which may require you to perform a left join.
You can then join all three subqueries to get your values.
SELECT A.DateValue, B.DateValue, C.DateValue
FROM
(SELECT T1.ConsigmentValue, T1.DateValue, T1.CardCode, T1.Farm1
FROM Table1 as T1
INNER JOIN (SELECT MaxValue.ConsigmentValue, MAX(MaxValue.DateValue) as MaxDateValue
FROM Table1 as MaxValue
GROUP BY MaxValue.ConsigmentValue) as MaxValue ON T1.ConsigmentValue = MaxValue.ConsigmentValue
AND T1.DateValue = MaxValue.DateValue) as A
INNER JOIN
(SELECT T2.ConsigmentValue, T2.DateValue, T2.CardCode, T2.Farm1
FROM Table2 as T2
INNER JOIN (SELECT MaxValue.ConsigmentValue, MAX(MaxValue.DateValue) as MaxDateValue
FROM Table2 as MaxValue
GROUP BY MaxValue.ConsigmentValue) as MaxValue ON T2.ConsigmentValue = MaxValue.ConsigmentValue
AND T2.DateValue = MaxValue.DateValue) as B ON A.ConsigmentValue = B.ConsigmentValue
INNER JOIN
(SELECT T3.ConsigmentValue, T3.DateValue, T3.CardCode, T3.Farm1
FROM Table3 as T3
INNER JOIN (SELECT MaxValue.ConsigmentValue, MAX(MaxValue.DateValue) as MaxDateValue
FROM Table3 as MaxValue
GROUP BY MaxValue.ConsigmentValue) as MaxValue ON T3.ConsigmentValue = MaxValue.ConsigmentValue
AND T3.DateValue = MaxValue.DateValue) as C ON A.ConsigmentValue = C.ConsigmentValue
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 5, 2011 at 8:50 am
Michael has some good questions. Replication, BTW, is not repetition or repeated rows. It also means something else in SQL Server.
I missed the sentence about duplicate values, and my apologies. However I don't see anything in there about missing the Consignment values. You have to have some value that will join tables together. If you don't, then how do you handle that? Is it guaranteed to be in one table?
April 11, 2011 at 12:51 am
Well, the consignment no. is the unique link on all the tables.
April 11, 2011 at 1:33 am
martin.edward (4/4/2011)
Dear Experts,I have 3 different tables that I would like to query data from and put it in the following format
ConsignmentValue Duedate CardCode Farm1 Farm2 Farm3
325 01/05/2011 C200006 500 200 300
340 01/05/2011 C200007 100
342 01/05/2011 C200032 50 100
The tables all have similar data but the column ConsignmentValue can be found in all 3 tables and it is used as the connector. Such that from Consignment Value 325 I should dislplay the Credit Data from the 3 Farms in the the layout above.
The problem I face is that where the consignment value has been repeated in the tables more than once where the duedate is diffrent I get replications which mess up my query, due to the large number of rows in the tables the query fails to execute.
Kindly Find the DDLs below.
CREATE TABLE #mytable1
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DateValue DATETIME,
MyValue DECIMAL(9,4),
Credit INT,
CARDCODE VARCHAR (20),
ConsigmentValue VARCHAR (20),
Farm1 Varchar (20),
)
CREATE TABLE #mytable2
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DateValue DATETIME,
MyValue DECIMAL(9,4),
Credit INT,
CARDCODE VARCHAR (20) INT,
ConsigmentValue VARCHAR (20),
Farm1 Varchar (20),
)
CREATE TABLE #mytable2
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DateValue DATETIME,
MyValue DECIMAL(9,4),
Credit INT,
CARDCODE VARCHAR (20) INT,
ConsigmentValue VARCHAR (20),
Farm1 Varchar (20),
)
// This should populate all 3 tables as the data held is similar.
SELECT '5766','May 1 2011 12:00AM','11206.800000','C200006','325','Farm1' UNION ALL
SELECT '5767','May 1 2011 12:00AM','11206.800000','C200007','325','Farm2' UNION ALL
SELECT '5768','May 1 2011 12:00AM','2460.760000','C200032','340','Farm3' UNION ALL
SELECT '5769','May 1 2011 12:00AM','2460.760000','C200118','342','Farm1' UNION ALL
SELECT '5770','May 1 2011 12:00AM','23696.240000','C200038','339','Farm2' UNION ALL
SELECT '5771','May 1 2011 12:00AM','35316.540000','C200033','338','Farm3' UNION ALL
SELECT '5772','May 1 2011 12:00AM','3873.430000','C200045','51','Farm3' UNION ALL
SELECT '5773','May 1 2011 12:00AM','6835.460000','C200117','51','Farm2'
Please help get solution for this.
Martin,
If you'll take the time to actually make the "DLL's" work and actually populate the tables with data, I'll try to lend a hand here. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply