Database

24-Jan-17 3:20

This is not very clear.

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.

JOIN Problem

Kevin Marois19-Jan-17 9:01

19-Jan-17 9:01

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

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.

ZurdoDev19-Jan-17 9:22

19-Jan-17 9:22

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.

Kevin Marois19-Jan-17 9:25

19-Jan-17 9:25

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.

ZurdoDev19-Jan-17 9:30

19-Jan-17 9:30

Kevin Marois wrote:
the BayLocation for each AttemptedRun record.

I believe that is what you have originally:

SQL

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 Marois19-Jan-17 9:32

19-Jan-17 9:32

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.

ZurdoDev19-Jan-17 9:35

19-Jan-17 9:35

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.

Kevin Marois19-Jan-17 9:45

19-Jan-17 9:45

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.

ZurdoDev19-Jan-17 9:50

19-Jan-17 9:50

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.

Kevin Marois19-Jan-17 9:51

19-Jan-17 9:51

Ya I learned that the hard way Poke tongue | ;-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 Poke tongue | ;-P

If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.

Eddy Vluggen19-Jan-17 9:38

Eddy Vluggen

19-Jan-17 9:38

Unless I mistake what you're asking, that should be doable using a correlated subquery. Something along the lines of

SQL

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 Suspicious | :suss:

If you can't read my code, try converting it here[^]

ZurdoDev19-Jan-17 9:52

19-Jan-17 9:52

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 need some help from my fellow developers...

Eddy Vluggen19-Jan-17 10:03

Eddy Vluggen

19-Jan-17 10:03

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 Suspicious | :suss:

If you can't read my code, try converting it here[^]

modified 19-Jan-17 16:49pm.

Steve Naidamast12-Jan-17 3:44

Steve Naidamast

12-Jan-17 3:44

Colleagues:

I have developed a product for Microsoft's SQL Server database. It is a source control tool very similar to what both Red-Gate and ApexSQL are offering but priced very affordably for individual developers. Unlike these two competing products, my software is designed with localized storage so that the files can be easily transferable to another machine that has my software installed.

The reason I developed this product was directly related to the many experiences I had in both my consulting assignments and full-time employment where I found that with the exception of those companies that had complete database groups, source control for applications was often poor and erratic while for database version control of SQL's object scripts it was non-existent.

On a number of occasions, while developing database applications and alongside that, the SQL procedures and modules to support these applications, I found my efforts hindered by others going in and changing those modules I was working on or even completely wiping them out without my ability to save them on a regular basis so that I could recover them if necessary.

Though my product has had several hundred downloads over the time I have been offering it I have received no sales. However, what has dismayed me even more is the lack of comments about my software that would give me an inkling as to why people who had downloaded the product chose not to pursue a purchase.

To date I have received one review by a download site that appeared to go through my software on a legitimate basis and liked it very much. Their only complaint was the "stodgy" interface, which I have since modernized.

I would like additional input to this effort to better understand if there is something in my work that is deficient or it is merely a marketing issue. As a result, I am offering 5 to 10 license keys to those developers that would take the time to review my product for me and give me some of the insight I am looking for.

My software is called, "SQL Server Source Control for Developers" and can be downloaded as a 30-day trial package from my business site... Black Falcon Software

These freely available keys will unlock the trial version making it a full version, which can be installed on up to 5 separate machines.

To obtain one of these licenses, simply contact me at my email address below and I will forward it to you.

If any of you would be willing to take the time out to work with my software and provide your comments to it as a result, it would be greatly appreciated...

Thank you...

Steve Naidamast
Sr. Software Engineer
Black Falcon Software, Inc.
blackfalconsoftware@outlook.com

Re: I need some help from my fellow developers...

Richard MacCutchan12-Jan-17 4:19

Richard MacCutchan

12-Jan-17 4:19

Wrong forum, this belongs at Collaboration / Beta Testing Discussion Boards[^].

Re: I need some help from my fellow developers...

Steve Naidamast12-Jan-17 5:08

Steve Naidamast

12-Jan-17 5:08

Thanks for the heads-up.

I'll move my thread over as soon as I finish my current set of tasks.

Steve Naidamast
Sr. Software Engineer
Black Falcon Software, Inc.
blackfalconsoftware@outlook.com

vb.net, select where statement

jkirkerx5-Jan-17 8:12

jkirkerx

5-Jan-17 8:12

I don't seem to be getting this right in VB. I get an error in casting the result as an Integer, in which the result may not exist.
ProductInfo_Inventory should be a join to get a count

Dim cStatus As Integer = context.ProductInfo_Inventory.Where(Function(m) m.partNumber = p_PartNumber).Select(Function(m) m.currentStatus).DefaultIfEmpty()

This is the whole thing. Not every item has a record in inventory for it's optional. So I'm not really sure how to properly handle this in VB
My new program is much better and in c#, so I seem to be doing better in that now.

pValue = context.ProductInfo.Where(Function(m) m.PartNumber = p_PartNumber).Count()
Dim cStatus = context.ProductInfo_Inventory.Where(Function(m) m.partNumber = p_PartNumber).Select(Function(m) m.currentStatus).DefaultIfEmpty()
If (cStatus = 4) Then
  dV = False
Else
  dV = If(pValue > 0, True, False)
End If

21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.

modified 5-Jan-17 14:27pm.

Wendelius5-Jan-17 8:30

Wendelius

5-Jan-17 8:30

The first question is, what is the type of currentStatus?. If it's non-numeric or beyond the limits of integer, the cast will fail.

Another thing is that if the current status is nullable (or reference type) then the default would be null which cannot be cast to integer.

The most accurate information is in the error message, but you didn't post that.

jkirkerx5-Jan-17 8:39

jkirkerx

5-Jan-17 8:39

I had a customer walk in while posting and I hit post without much details, sorry about that; my bad.
This just determines if an item really exist, in case someone messed with the query string and put bad chars in there, or if an item was marked for deletion.

I went with this; cStatus is an Integer.

pValue = context.ProductInfo.Where(Function(m) m.PartNumber = p_PartNumber).Count()
If (context.ProductInfo_Inventory.Any(Function(m) m.partNumber = p_PartNumber)) Then
   cStatus = context.ProductInfo_Inventory.Where(Function(m) m.partNumber = p_PartNumber).Select(Function(m) m.currentStatus).FirstOrDefault()
End If

If (cStatus = 4) Then
   dV = False
Else
   dV = If(pValue < 4, True, False)
End If

21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.

Wendelius5-Jan-17 8:47

Wendelius

5-Jan-17 8:47

No problem! So everything works now? Just notice that FirstOrDefault may also return null which wuold cause an exception since cStatus is integer so depending on the situation you may want to use integer? and check if a value is actually returned.