1,105,380 Community Members

SQL Query to join two tables

Member Avatar
vallikasturi
Newbie Poster
10 posts since May 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi...
I have two tables without primary keys and with a common field meterid...
I can't add a primary key to any of these tables...
Now i need to join both tables..
Can anyone write the query for this...plss...

select * from table1,table2 where table1.meterid=table2.meterid; isn't working...
The result of the above query is the multiple of rows...

Thanks in advance

Member Avatar
JamesLogan
Newbie Poster
10 posts since May 2010
Reputation Points: -1 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

select * from table1,table2 where table1.meterid=table2.meterid;

try this!

SELECT table1.meterid, table2.meterid FROM table1,table2
WHERE table1.meterid = table2.meterid
GROUP BY table1.meterid

Member Avatar
JamesLogan
Newbie Poster
10 posts since May 2010
Reputation Points: -1 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

or try this one also.


SELECT table1.meterid, table2.meterid FROM table1 JOIN table2
ON table1.meterid = table2.meterid
GROUP BY table1.meterid


any doubts ask me.

Member Avatar
vallikasturi
Newbie Poster
10 posts since May 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

I need all the repeated meterids in table1 with its appropriate details in table2...

The query u submitted is removing the duplicates of the meterid...
it shouldn't be like that

Member Avatar
bhartman21
Light Poster
41 posts since May 2010
Reputation Points: 2 [?]
Q&As Helped to Solve: 8 [?]
Skill Endorsements: 0 [?]
 
0
 

I need all the repeated meterids in table1 with its appropriate details in table2...

The query u submitted is removing the duplicates of the meterid...
it shouldn't be like that

If you have a unique field in table2, you could do something like this...

SELECT table1.meterid, table2.meterid, table2.uniqueField 
FROM table1 
        JOIN table2 ON table1.meterid = table2.meterid
GROUP BY table1.meterid, table2.uniqueField
ORDER BY table1.meterid, table2.uniqueField

OR even use an OUTER JOIN

SELECT table1.meterid, table2.meterid 
FROM table1 
        LEFT OUTER JOIN table2 ON table1.meterid = table2.meterid  
ORDER BY table1.meterid, table2.uniqueField
Member Avatar
vallikasturi
Newbie Poster
10 posts since May 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

There is no unique field in any of my two tables...
and i dont have to add the unique fields...

Member Avatar
bhartman21
Light Poster
41 posts since May 2010
Reputation Points: 2 [?]
Q&As Helped to Solve: 8 [?]
Skill Endorsements: 0 [?]
 
0
 

There is no unique field in any of my two tables...
and i dont have to add the unique fields...

Then can you provide a sample of each of your tables....something like this....

table1 (table name)
table1.meterid (column name 1)
table1.meterName (column name 2)
table1.meterDesc (column name 3)
table1.meterTitle (column name 4)

table2 (table name)
table2.meterid (column name 1)
table2.meterDetail (column name 2)
table2.meterDetailDesc (column name 3)
table2.meterDetailTitle (column name 4)

....it would make it a bit easier to view...you said when you do your grouping, you lose the details from table2....so something is needed from table2 which may not be unique, but it is unique to that meterId.

Member Avatar
vallikasturi
Newbie Poster
10 posts since May 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 
CREATE TABLE  `mdas`.`billingdata` (
  `MeterId` varchar(10) DEFAULT NULL,
  `StartTime` time DEFAULT NULL,
  `StartDate` date DEFAULT NULL,
  `ActiveImportTotal` varchar(10) DEFAULT NULL,
  `ApparentForwarded` varchar(10) DEFAULT NULL,
  `ReactiveImportLead` varchar(10) DEFAULT NULL,
  `ReactiveImportLag` varchar(10) DEFAULT NULL,
  `ActiveExportVoltage` varchar(10) DEFAULT NULL,
  `ApparentExport` varchar(10) DEFAULT NULL,
  `ReactiveExportLead` varchar(10) DEFAULT NULL,
  `ReactiveExportLag` varchar(10) DEFAULT NULL,
  `ActiveForwarded` varchar(10) DEFAULT '-NA-',
  `ActiveImportVoltage` varchar(10) DEFAULT '-NA-',
  `ApparentActiveImportVoltage` varchar(10) DEFAULT '-NA-',
  `ReactiveImportEnergy` varchar(10) DEFAULT '-NA-',
  `ReactiveExportEnergy` varchar(10) DEFAULT '-NA-',
  `ActiveForwardedEnergy` varchar(10) DEFAULT '-NA-',
  `ApparentForwardedEnergy` varchar(10) DEFAULT '-NA-',
  `CMDReactiveDemandImportEnergy` varchar(10) DEFAULT NULL,
  `CMDReactiveDemandExportEnergy` varchar(10) DEFAULT NULL,
  `CMDActiveDemandImportEnergy` varchar(10) DEFAULT NULL,
  `CMDApparenetDemandImportEnergy` varchar(10) DEFAULT NULL,
  `TODMDReactiveDemandImportEnergy` varchar(10) DEFAULT NULL,
  `TODMDReactiveDemandExportEnergy` varchar(10) DEFAULT NULL,
  `TODMDActiveDemandImportEnergy` varchar(10) DEFAULT NULL,
  `TODMDApparentDemandImportEnergy` varchar(10) DEFAULT NULL,
  `ActiveFundamentalImport` varchar(10) DEFAULT NULL,
  `ActiveFundamentalExport` varchar(10) DEFAULT NULL,
  `ActiveDefrauded` varchar(10) DEFAULT NULL,
  `ApparentDefrauded` varchar(10) DEFAULT NULL,
  KEY `MeterId` (`MeterId`),
  CONSTRAINT `billingdata_ibfk_1` FOREIGN KEY (`MeterId`) REFERENCES `meter` (`meterid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


CREATE TABLE  `mdas`.`meterinfo` (
  `MeterId` varchar(10) NOT NULL,
  `TimeOfReading` time DEFAULT NULL,
  `DateOfReading` date DEFAULT NULL,
  `TimeOfDataDump` time DEFAULT NULL,
  `DateOfDataDump` date DEFAULT NULL,
  `VoltageScalling` varchar(5) DEFAULT NULL,
  `VoltageScallingExponent` varchar(5) DEFAULT NULL,
  `CurrentScallingExponent` varchar(5) DEFAULT NULL,
  `CurrentScallingDivisor` varchar(5) DEFAULT NULL,
  `PowerScallingExponent` varchar(5) DEFAULT NULL,
  `PowerScallingDivisor` varchar(5) DEFAULT NULL,
  `EnergyMultiplierExponent` varchar(5) DEFAULT NULL,
  `DemandDivisor` varchar(5) DEFAULT NULL,
  `MeterSoftwareName` varchar(20) DEFAULT NULL,
  `FirmwareName` varchar(20) DEFAULT NULL,
  `ModbusProtocolVersion` varchar(10) DEFAULT NULL,
  `CumulativePowerONminutes` varchar(10) DEFAULT NULL,
  `CumulativePowerOFFminutes` varchar(10) DEFAULT NULL,
  KEY `meterid` (`MeterId`) USING BTREE,
  CONSTRAINT `meterinfo_ibfk_1` FOREIGN KEY (`MeterId`) REFERENCES `meter` (`meterid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

These are the two tables....i need to join

Member Avatar
bhartman21
Light Poster
41 posts since May 2010
Reputation Points: 2 [?]
Q&As Helped to Solve: 8 [?]
Skill Endorsements: 0 [?]
 
0
 

Ok...assuming billingData = table1 and meterInfo = table2 (with the details for the billingData) ....and one other assumption is that there is one record in the billingData table and multiple records in the meterInfo table.

SELECT *
FROM billingData
LEFT OUTER JOIN meterInfo ON billingData.meterId = meterInfo.meterId
ORDER BY billingData.meterId

For testing purposes add the following WHERE clause...
WHERE meterInfo.meterID = '1' -- (or any one specific meterId)

...this will help narrow things down a bit

Member Avatar
vallikasturi
Newbie Poster
10 posts since May 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Noooo.... It didn't worked...
i have 5000 rows in both the tables
But i'm getting more than 100000 rows...

Member Avatar
bhartman21
Light Poster
41 posts since May 2010
Reputation Points: 2 [?]
Q&As Helped to Solve: 8 [?]
Skill Endorsements: 0 [?]
 
0
 

Noooo.... It didn't worked...
i have 5000 rows in both the tables
But i'm getting more than 100000 rows...

Ok...a couple direct questions...
1. How many unique meterId records do you have in the billingData table? ....do this...

SELECT COUNT(DISTINCT meterId) FROM billingData

to get a DISTINCT results count....then this...

SELECT COUNT(meterId) FROM billingData

to get a total result count.
2. How many records from meterInfo are you expecting to link back to one record in the billingData table? ....do this...

DECLARE @anySpecificMeterId varchar(10)
SET @anySpecificMeterId = (SELECT TOP 1 meterId FROM billingData)

SELECT COUNT(meterId) FROM meterInfo WHERE meterId = '@anySpecificMeterId'

...then let me know the results of the queries.

Also, give the query below a try.

SELECT [B]DISTINCT[/B] * 
FROM billingData 
[B]RIGHT[/B] OUTER JOIN meterInfo ON billingData.meterId = meterInfo.meterId
ORDER BY billingData.meterId
Member Avatar
vallikasturi
Newbie Poster
10 posts since May 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

1. 20
2. 4931
and 3rd query isn't executing in mysql...
and 4th query result is fetching more than 100000 rows..

Member Avatar
bhartman21
Light Poster
41 posts since May 2010
Reputation Points: 2 [?]
Q&As Helped to Solve: 8 [?]
Skill Endorsements: 0 [?]
 
0
 

1. 20
2. 4931
and 3rd query isn't executing in mysql...
and 4th query result is fetching more than 100000 rows..

Ok...the 3rd query isn't executing because it was syntax for MS SQL, instead of mySQL (I was going by the fact this is posted in the MS SQL forum....sorry about that).

Try this to get the same results...

SELECT COUNT(meterId) FROM meterInfo WHERE meterId = (SELECT TOP 1 meterId FROM billingData)
Member Avatar
vallikasturi
Newbie Poster
10 posts since May 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Still there's a syntax error... at top 1

Member Avatar
bhartman21
Light Poster
41 posts since May 2010
Reputation Points: 2 [?]
Q&As Helped to Solve: 8 [?]
Skill Endorsements: 0 [?]
 
0
 

Still there's a syntax error... at top 1

vallikasturi....you're really not giving me much to go on.
1. can you at least figure out how to get that query to run and give me the results.
2. I am still uncertain which table1 and table2 represent.
3. You said the following....
... 20 unique meterIds in the billingData table.
... 4931 unique meterIds in the meterInfo table.
.... in a previous post, you stated you have 5000 records in each table....so if you don't use a GROUP BY clause or a DISTINCT in your SELECT statement, you will always have duplicates...earlier you stated that the GROUP BY removed all duplicates, but the only columns you were pulling were table1.meterId and table2.meterId. That will reduce your results to the bare minimum....that's why you need to select more columns.

At this time, I'd like to recommend an awesome book for learning SQL. It's a Sams Publishing book titled: Teach Yourself SQL in 10 Minutes

http://www.amazon.com/Sams-Teach-Yourself-SQL-Minutes/dp/0672325675
I have bought several of these books now.....I loan them out and never get them back. I have also seen that there is a PDF out there on the internet somewhere.

Anyways....I wish you luck in solving your problem. I will be more than happy to continue helping you if you can provide some more helpful information to your problem....like the results you are expecting vs what you are getting, why you think the results are incorrect, etc.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article