|
which version of SQL server are you using?
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
hi,
I am using SQL Server 2005 database.
I stored Thumb Image on two tables to the Image data type field.
When I compare these two images from both table is gives error...
"The data types image and image are incompatible in the equal to operator"
Statement :
"Select FP1 From E_CH16APPLICANTS Where FP1 In (Select Photo From Tmp)"
I just like to verify that this Thumb Image is exist or not...
by using SQL Statement...
Pls help me...
|
|
|
|
|
check for NULL or DBNULL, or use IsNull; or something like that. No need to actually compare anything if I understood your question correctly.
|
|
|
|
|
Exactly I want to know...
Can I Compare two Images stored in the Table...?????
|
|
|
|
|
I'm no database expert at all, however I've never seen SQL code that compares big fields (blobs, images), and I expect that whenever you (think you) need it, you have a bad design to start with; there should be no doubt that the image, when present, is the right one (the only doubt I would allow for is whether the image is present at all); and IMO it would most always be wise to have a separate field that uniquely identifies the image (a hash or CRC would probably be fine).
The only way I know to compare two blobs/images is by using a programming language (say C#), fetch the two blobs and compare their elements in a loop.
|
|
|
|
|
One LIKE operation may help you to compare two image field.
Here [^] is a link on Brief Tutorial on Text, Ntext, and Image. You will also find a example how to compare two image column
|
|
|
|
|
Convert Image field in Varbinary(Max)
Ex:
Select * From E_CH16APPLICANTS
Where (Convert(VARBINARY(MAX), FP1) In (Select Convert(VARBINARY(MAX), Photo) From Tmp)
|
|
|
|
|
Never use select * . As a shortcut, it leads to bad programming habits and it can lead to hard to find bugs as columns get renamed in the underlying database. You should always choose to explicitly select columns instead, partly because using
select * typically results in columns being retrieved that aren't needed in the result set.
|
|
|
|
|
Manish_84 wrote: Convert Image field in Varbinary(Max)
It'd be 'sweet' if you could create a hash to represent your image, and store that in a column next to the image itself
I are Troll
|
|
|
|
|
Does anyone know of a good reference site for SQL Server 2005 Notification Services?
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
Are you thinking of using them? I ask because Notification Services were killed off in SQL Server 2008, which would make an upgrade path tricky to say the least.
|
|
|
|
|
Thanks Pete.. just Google'd that and as its part of the report services I might be rethinking that part that I was going to use notification services
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
Hey all,
I am still fairly knew to SQL query writing, and have searched to try and solve this by myself, however have had no luck!
I am trying to write a query that returns information from SaleInformation and File_Info, with SaleInformation including all records that match a query and File_Info returning the matching File_Name(I have a relationship between them with PID the primary key in File_Info) if the PID is a match.
However, my query is returning a result in every File_Name row return, even though I only have 3 entries in the File_Info PID column that match only 3 records in the Sale_information table. A gentle push in the right direction would be greatly appreciated!
Oh, on a side note - If I change the query to "ON File_Info.PID = SaleInformation.PID" I get the 3 records that match returned. Here is the query I have at the moment:
USE [C:\DATABASE\SALES.MDF]
GO
SELECT File_Info.File_Name, SaleInformation.PID, SaleInformation.Sale_date, SaleInformation.Sale_price
FROM File_Info LEFT OUTER JOIN SaleInformation
ON SaleInformation.Sale_price > 150000
It returns 183 rows. There are only 94 rows in the SaleInformation table and 3 in the File_Info table. I am very confused!
Regards,
Joe
|
|
|
|
|
Joe Stansfield wrote: I have a relationship between them with PID the primary key in File_Info
Perhaps you think that that will somehow automatically affect the join? It doesn't; you need to specify the relationship in the join.
|
|
|
|
|
Ahh thanks! That is exactly what I thought would happen...
Back to the old drawing board!
|
|
|
|
|
As Piebald suggest, you need the relationship in the join. You can include the filter as well but the relationship must be there.
SELECT File_Info.File_Name, SaleInformation.PID, SaleInformation.Sale_date, SaleInformation.Sale_price
FROM File_Info LEFT OUTER JOIN SaleInformation ON File_Info.PID = SaleInformation.PID
and SaleInformation.Sale_price > 150000
or
SELECT File_Info.File_Name, SaleInformation.PID, SaleInformation.Sale_date, SaleInformation.Sale_price
FROM File_Info LEFT OUTER JOIN SaleInformation ON File_Info.PID = SaleInformation.PID
WHERE SaleInformation.Sale_price > 150000
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks!
I had some trouble making it work - but it all came down to simply having the tables on the wrong sides of the LEFT OUTER JOIN. Switched them around and it is return as I wanted!
Cheers for the assistance.
|
|
|
|
|
This is one of the most useful articles [^]about joins I have seen
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks again!
Great article - definitely helped me understand.
|
|
|
|
|
I am using C# with MySQL..
I want to know what's the best way to save the System.Color in mySQL?
|
|
|
|
|
Try saving as argb
color.Red.ToArgb().ToString
or in HEX
color.Red.ToArgb().ToString ( "X8" )
Here [^] is a good example of serializing color. Take a look at the PIEBALDconsult's 2nd reply. Although the original thread is about returning color from webservice but still you could get an idea about it. I loved that post
|
|
|
|
|
yeah, I liked that thread too. I would however have preferred System.Color to serialize just like a simple integer all by itself!
|
|
|
|
|
Excellent answer! I was about to same the same thing.
|
|
|
|
|
How can I select a random record in mySQL,. something similar to the ORDER BY newid() in MS SQL?
|
|
|
|
|