I need help with a SQL problem

15 pts.
Tags:
SQL
I have a SQL statement that reads as follows:
select subject1, subject2, subject3, subject4, subject5, subject6
from card_catalog
where subject1 like?
This SQL statement works I just do not have the statement in front of me to type it in correctly. Is what my real question is. I have a TEXTBOX that stores the information and then passes it to the SQL statement and searches for the data subject1 in the database. I need it to also search at the same time in fields: subject2, subject3, subject4, subject5 and subject6. I tried using the (AND, OR) they are not giving me what I need and I can not get union, = to work what do I need to do to get it to search correctly. I hope you know what I am talking about. If you need more information I can send it. STEPHEN

Answer Wiki

Thanks. We'll let you know when a new response is added.

Something like this should do the trick.

DECLARE @Find VARCHAR(50)
SET @Find = '%the%'

SELECT Subject1, Subject2, Subject3, Subject4, Subject5, Subject6
FROM catd_catalog
WHERE Subject1 LIKE @Find
OR Subject2 LIKE @Find
OR Subject3 LIKE @Find
OR Subject4 LIKE @Find
OR Subject5 LIKE @Find
OR Subject6 LIKE @Find

Now if you have NULL values in your columns this won’t work quite right. You’ll need something more like this.

DECLARE @Find VARCHAR(50)
SET @Find = '%the%'

SELECT Subject1, Subject2, Subject3, Subject4, Subject5, Subject6
FROM card_catalog
WHERE Subject1 LIKE @Find
OR (Subject2 IS NOT NULL AND Subject2 LIKE @Find)
OR (Subject3 IS NOT NULL AND Subject3 LIKE @Find)
OR (Subject4 IS NOT NULL AND Subject4 LIKE @Find)
OR (Subject5 IS NOT NULL AND Subject5 LIKE @Find)
OR (Subject6 IS NOT NULL AND Subject6 LIKE @Find)

You may want to look into a database redesign project. This isn’t the most effective database design. You’ll want a table which is vertical.

CREATE TABLE card_catalog
(BookId INT,
SubjectNumber INT,
Subject VARCHAR(50)
CONSTRAINT PK_card_catalog (BookId, SubjectNumber))

You can then use the PIVOT statement to return the data back to your client code so that your application doesn’t require a change (assuming you are using SQL 2005, if not other techniques can be used).

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Yes we need more info. The AND and OR logic operators produce different results, so you need to clearly define whether you want to get recrods in which ALL conditions are met (AND) or records in which at least one condition is met (OR). To be able to help, I think we need to see the exact SQL statement, and we need you to explain what are the results you expect, and if possible, post some example data.
    68,015 pointsBadges:
    report
  • COMPCAD
    Here is the exact code: Dim conn As New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:Documents and SettingsOwnerDesktopcatalogcatalogdatadb181.mdb") Dim adapter As New OleDbDataAdapter() Dim command As OleDbCommand ' Create the SelectCommand. command = New OleDbCommand("select subject1, subject2, subject3, subject4, subject5, subject6 from card_catalog Where subject1 like ?", conn) command.CommandType = CommandType.Text command.Parameters.Add("subject1", OleDbType.VarChar, 255) 'command.Parameters.Add("subject2", OleDbType.VarChar, 255) 'command.Parameters.Add("subject3", OleDbType.VarChar, 255) 'command.Parameters.Add("subject4", OleDbType.VarChar, 255) 'command.Parameters.Add("subject5", OleDbType.VarChar, 255) 'command.Parameters.Add("subject6", OleDbType.VarChar, 255) command.Parameters(0).Value = "%" + frmSearch.txtSubject.Text + "%" adapter.SelectCommand = command adapter.Fill(ds, "card_catalog") datResults.DataSource = ds.Tables(0) datResults.AlternatingRowsDefaultCellStyle.BackColor = Color.LightBlue subjet1 subjet2 subjet3 subjet4 subjet5 subjet6 tax... tax... ...tax... ...tax ...tax tax... tax... tax ...tax... ...tax ...tax... tax... ...tax... ...tax... tax tax... ...tax ...tax tax... ...tax ...tax tax tax... ...tax ...tax tax... ...tax tax... ...tax ...tax ...tax tax... tax... ...tax ...tax ...tax I hope you can understand compcad@aol.com Stephen
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following