JOIN Problem
-
SQL 2012: I have two tables, AttemptedRuns and Bays This
SELECT *
FROM NexGen.dbo.AttemptedRuns
WHERE BaySerialNumber = '1545300531'Produces 317 rows. The table has a column called 'BaySerialNumber' So I want to join on bays so I can retrieve the BayLocation:
SELECT ar.Id as AttemptedRunId,
ar.AccessionId,
ar.StartTime,
ar.BaySerialNumber,
ar.CartridgeId,
ar.Result as AttemptedRunResult,
b.Location as BayLocation
FROM NexGen.dbo.AttemptedRuns ar
LEFT JOIN NexGen.dbo.Bays b on b.SerialNumber = ar.BaySerialNumber
WHERE ar.BaySerialNumber = '1545300531'The problem is that I now get 1902 rows. It's duplicating the AttemptedRuns rows. [See here](https://1drv.ms/i/s!AlkRTpT49yCMgiGkgyxj747ZTe1d) What I am looking for is to get the BayLocation from the Bays table for each AttemptedRow row. What am I doing wrong??
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
There are more matches than 1 to 1. If you change it to SELECT * you'll see all the fields in both tables and can then see why you are getting more than one match.
There are two kinds of people in the world: those who can extrapolate from incomplete data. There are only 10 types of people in the world, those who understand binary and those who don't.
-
There are more matches than 1 to 1. If you change it to SELECT * you'll see all the fields in both tables and can then see why you are getting more than one match.
There are two kinds of people in the world: those who can extrapolate from incomplete data. There are only 10 types of people in the world, those who understand binary and those who don't.
OK, but what I really want is to just get the BayLocation for each AttemptedRun record. Is this possible?
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
OK, but what I really want is to just get the BayLocation for each AttemptedRun record. Is this possible?
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
Kevin Marois wrote:
the BayLocation for each AttemptedRun record.
I believe that is what you have originally:
SELECT ar.Id as AttemptedRunId,
ar.AccessionId,
ar.StartTime,
ar.BaySerialNumber,
ar.CartridgeId,
ar.Result as AttemptedRunResult,
b.Location as BayLocation
FROM AttemptedRuns ar
LEFT JOIN Bays b on b.SerialNumber = ar.BaySerialNumber
WHERE ar.BaySerialNumber = '1545300531'This says to get all attempted runs and if there is a bay that matches on BaySerialNumber to get its information as well.
There are two kinds of people in the world: those who can extrapolate from incomplete data. There are only 10 types of people in the world, those who understand binary and those who don't.
-
Kevin Marois wrote:
the BayLocation for each AttemptedRun record.
I believe that is what you have originally:
SELECT ar.Id as AttemptedRunId,
ar.AccessionId,
ar.StartTime,
ar.BaySerialNumber,
ar.CartridgeId,
ar.Result as AttemptedRunResult,
b.Location as BayLocation
FROM AttemptedRuns ar
LEFT JOIN Bays b on b.SerialNumber = ar.BaySerialNumber
WHERE ar.BaySerialNumber = '1545300531'This says to get all attempted runs and if there is a bay that matches on BaySerialNumber to get its information as well.
There are two kinds of people in the world: those who can extrapolate from incomplete data. There are only 10 types of people in the world, those who understand binary and those who don't.
RyanDev wrote:
This says to get all attempted runs and if there is a bay that matches on BaySerialNumber to get its information as well.
Correct, What I'm looking for is the APPEND the BayLocation to the end of each row of AttemptedRun data
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
RyanDev wrote:
This says to get all attempted runs and if there is a bay that matches on BaySerialNumber to get its information as well.
Correct, What I'm looking for is the APPEND the BayLocation to the end of each row of AttemptedRun data
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
But you have 6 matching rows in Bays. BaySerialNumber is not unique. That is why you need to run SELECT * and see why you are getting extra. Perhaps there is another field, like status, that you need to take into account. We can't know because we can't see all the data.
There are two kinds of people in the world: those who can extrapolate from incomplete data. There are only 10 types of people in the world, those who understand binary and those who don't.
-
OK, but what I really want is to just get the BayLocation for each AttemptedRun record. Is this possible?
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
Unless I mistake what you're asking, that should be doable using a correlated subquery. Something along the lines of
SELECT *, -- 'everything from the attemptedrun record
(SELECT BayLocation FROM NexGen.dbo.Bays AS bl WHERE bl.SerialNumber = ar.BaySerialNumber) AS [MyNewColumn]
FROM NexGen.dbo.AttemptedRun AS ar
WHERE BaySerialNumber = '1545300531'That is assuming that each bay is uniquely numbered on said field.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
But you have 6 matching rows in Bays. BaySerialNumber is not unique. That is why you need to run SELECT * and see why you are getting extra. Perhaps there is another field, like status, that you need to take into account. We can't know because we can't see all the data.
There are two kinds of people in the world: those who can extrapolate from incomplete data. There are only 10 types of people in the world, those who understand binary and those who don't.
Well you are right. Turns out there are 6 rows in Bays with the same serial number. I was operating under the assumption that the serial number was unique. Thanks
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
Well you are right. Turns out there are 6 rows in Bays with the same serial number. I was operating under the assumption that the serial number was unique. Thanks
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
No problem. You may find that you need to use a different column to ensure uniqueness. Or there may be another table altogether. Who knows. Good luck.
There are two kinds of people in the world: those who can extrapolate from incomplete data. There are only 10 types of people in the world, those who understand binary and those who don't.
-
No problem. You may find that you need to use a different column to ensure uniqueness. Or there may be another table altogether. Who knows. Good luck.
There are two kinds of people in the world: those who can extrapolate from incomplete data. There are only 10 types of people in the world, those who understand binary and those who don't.
Ya I learned that the hard way ;P First, I haven't done SQL in a while, so it's a bit blurry. Second, I don't really know the data too well. it's all fun & games now ;P
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
Unless I mistake what you're asking, that should be doable using a correlated subquery. Something along the lines of
SELECT *, -- 'everything from the attemptedrun record
(SELECT BayLocation FROM NexGen.dbo.Bays AS bl WHERE bl.SerialNumber = ar.BaySerialNumber) AS [MyNewColumn]
FROM NexGen.dbo.AttemptedRun AS ar
WHERE BaySerialNumber = '1545300531'That is assuming that each bay is uniquely numbered on said field.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
If you look at the data in his screenshot you'll see that there are different values for BayLocation per run so this approach would give an error about multiple records in a subquery.
There are two kinds of people in the world: those who can extrapolate from incomplete data. There are only 10 types of people in the world, those who understand binary and those who don't.
-
If you look at the data in his screenshot you'll see that there are different values for BayLocation per run so this approach would give an error about multiple records in a subquery.
There are two kinds of people in the world: those who can extrapolate from incomplete data. There are only 10 types of people in the world, those who understand binary and those who don't.
I didn't look; he said he wanted the entire record, and add a single column. Means it is also easy to debug the subquery by using a value from the database as example. There will probably be multiple records with the same ar.BaySerialNumber, and I'm hoping that b.SerialNumber is the primary key (or at least uniquely indexed) on the Bays-table. Even if there's a one to one relation, that should work. So, what the schema of the tables? Would be nice to have a CREATE script for both tables :) --edit Asking the wrong person, and was already solved.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)