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
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
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.
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
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.uniqueFieldOR 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.uniqueFieldThere is no unique field in any of my two tables...
and i dont have to add the unique fields...
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.
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=latin1These are the two tables....i need to join
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
Noooo.... It didn't worked...
i have 5000 rows in both the tables
But i'm getting more than 100000 rows...
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 billingDatato get a DISTINCT results count....then this...
SELECT COUNT(meterId) FROM billingDatato 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.meterId1. 20
2. 4931
and 3rd query isn't executing in mysql...
and 4th query result is fetching more than 100000 rows..
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)Still there's a syntax error... at top 1
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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.