473,372 Members | 828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

strange query result, (i must be doing something wrong)

1,047 Expert 1GB
Expand|Select|Wrap|Line Numbers
  1. SELECT id, int1 
  2. FROM testInt 
  3. WHERE int1 IN (1,2,346,596,597)
  4. ORDER BY int1, id
  5.  
  6. SELECT id, int1 
  7. FROM testInt 
  8. WHERE int1 IN (1,597)
  9. ORDER BY int1, id
  10.  
  11. SELECT id, int1 
  12. FROM testInt 
  13. WHERE id in (5152426,6301653,1308712,5518275,6121956,7410050)
  14. ORDER BY int1, id
  15.  
this gives next results
Expand|Select|Wrap|Line Numbers
  1. id          int1
  2. ----------- -----------
  3. 5152426     2
  4. 6301653     2
  5. 1308712     346
  6. 5518275     346
  7. 6121956     596
  8. 7410050     596
  9.  
  10. (6 rows affected)
  11.  
  12.  
  13.  
  14. (1 row affected)
  15. id          int1
  16. ----------- -----------
  17.  
  18. (0 rows affected)
  19.  
  20.  
  21.  
  22. (1 row affected)
  23. id          int1
  24. ----------- -----------
  25. 5152426     2
  26. 6301653     2
  27. 1308712     346
  28. 5518275     346
  29. 6121956     596
  30. 7410050     596
As you can see
- There is no record for int1 with value =1
- Why does the first query return two records with value=2?
- There is no record for int1 with value =597
- Why does the first query return two records with value=596?


The create script for this table is
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE [dbo].[testInt](
  2.     [id] [int] NOT NULL,
  3.     [description] [nvarchar](50) NULL,
  4.     [int1] [int] NULL,
  5.     [int2] [int] NULL,
  6.  CONSTRAINT [PK_testInt] PRIMARY KEY CLUSTERED 
  7. (
  8.     [id] ASC
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  10. ) ON [PRIMARY]
  11. GO
  12. CREATE NONCLUSTERED INDEX [NonClusteredIndex-20190309-125532] ON [dbo].[testInt]
  13. (
  14.     [int1] ASC
  15. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  16. GO
  17.  
  18.  
Mar 9 '19 #1

✓ answered by Rabbit

It seems to me that the int1 values were inserted multiple times with the same value. Since the additional index created on int1 doesn't specify unique, nothing prevents someone from doing that.

2 2418
Rabbit
12,516 Expert Mod 8TB
It seems to me that the int1 values were inserted multiple times with the same value. Since the additional index created on int1 doesn't specify unique, nothing prevents someone from doing that.
Mar 11 '19 #2
Luuk
1,047 Expert 1GB
Indeed
Expand|Select|Wrap|Line Numbers
  1. with tmp as (
  2.     SELECT 1 as tmp
  3.     union
  4.     select 2
  5.     union 
  6.     select 346
  7.     union
  8.     select 596
  9.     union 
  10.     select 597
  11. )
  12. SELECT tmp.tmp, id, int1 
  13. FROM tmp
  14. LEFT OUTER JOIN testInt on int1 = tmp.tmp
  15. ORDER BY int1, id
  16.  
shows:
Expand|Select|Wrap|Line Numbers
  1. tmp    id    int1
  2. 1    NULL    NULL
  3. 597    NULL    NULL
  4. 2    5152426    2
  5. 2    6301653    2
  6. 346    1308712    346
  7. 346    5518275    346
  8. 596    6121956    596
  9. 596    7410050    596
Mar 15 '19 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Mark | last post by:
I'm betting it me. Here is the simple schema I'm using: <?xml version="1.0"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:jaxb="http://java.sun.com/xml/ns/jaxb"...
3
by: Charles May | last post by:
I have a listview which checkboxes containing items to invoice. The Create Invoice button (button1) is disabled unless there are items checked. However, I had to use an if..then..else statement to...
1
by: Chiller | last post by:
Ok, I've implemented a few changes to the code and the bool functions now seem to be functioning correctly; however, I think I'm doing the convertions incorrectly because the values printed out...
4
by: bissatch | last post by:
I am trying to use DIV tags and a class to hide the DIV and the HTML within. I will use JavScript to change it from hidden to visible but that will come later. Below is the code I am using ...
6
by: Michael Sparks | last post by:
Hi, I suspect this is a bug with AMK's Crypto package from http://www.amk.ca/python/code/crypto , but want to check to see if I'm being dumb before posting a bug report. I'm looking at...
1
by: MLH | last post by:
When the following runs, I get error saying "Jet can't find input table/query qdfMarkedAddnlOwnerRecs" The error occurs in line #150 Sub cmdSaveVehicleRec_Click() frmVehicleEntryForm. 120 ...
9
by: Randy | last post by:
Hi all, I've been working on a simple two column layout for a site, but according to browsershots.org, my desing is not showing up correctly in Win/IE 5.01, 5.5 & 6 ! Please see details here:...
5
by: Simon Brooke | last post by:
This is supposed to be a very simple XSL stylesheet to strip styling information out of HTML documents - it could not be more basic. And yet, it doesn't work. I'm obviously getting something very...
0
by: Pat | last post by:
Hi Guys, Please help me. I am having a strange issue. Not sure if I am doing something wrong or this is a bug. This is a sample code. This contains 2 text boxes(txtName, txtPhone) and 2 buttons...
4
by: Wavey | last post by:
Hi All, I have a C# test app that writes to an access database with the following code DataRow editRow1 = ds.Tables.Rows.Find("1"); DataRow editRow2 = ds.Tables.Rows.Find("2"); editRow2 =...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.