5 Replies - 817 Views - Last Post: 13 April 2016 - 04:21 AM

#1 SoundWaves   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 01-June 15

Trouble with Case statement with select

Posted 11 April 2016 - 12:38 PM

OK, I have a very simple table that has one field that can be populated with either a file path or a Id Number of a document. A standard select statement works fine, but I would like to display the file name that corresponds to the Id number when it is used.

The field in the case statement - Link_Type is the identifier whether I need to query the second database table. I am trying to use the following select statement, I get a null value from my sub query and my total results are multiplied by the qty of documents in the second table.

Hopefully that made enough sense that you can interpret my attempt.
SELECT Machine_Links.Button_Id, Machine_Links.Machine_Id, Machine_Links.Button_Position, Machine_Links.Button_Name, Machine_Links.Button_Type, Machine_Links.Button_Link, 
CASE WHEN Machine_Links.Button_Type = 'QCBD_Link' THEN (SELECT QCBD_Files.Doc_Name WHERE QCBD_Files.Doc_Id=Machine_Links.Button_Link)
            ELSE Machine_Links.Button_Link END AS Link_Text
FROM Machine_Links, QCBD_Files WHERE Machine_Links.Machine_Id = 6


Is This A Good Question/Topic? 0
  • +

Replies To: Trouble with Case statement with select

#2 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: Trouble with Case statement with select

Posted 11 April 2016 - 09:12 PM

Well, your SQL is pretty messy and not really doing what you want by the looks. Assuming the join between Machine_Links and QCBD_Files does not generate duplicate rows, you're probably after something like this:
SELECT Machine_Links.Button_Id, Machine_Links.Machine_Id, Machine_Links.Button_Position, Machine_Links.Button_Name, Machine_Links.Button_Type, Machine_Links.Button_Link, 
CASE WHEN Machine_Links.Button_Type = 'QCBD_Link'
	THEN QCBD_Files.Doc_Name
	ELSE Machine_Links.Button_Link
END AS Link_Text
FROM Machine_Links
LEFT OUTER JOIN QCBD_Files ON QCBD_Files.Doc_Id=Machine_Links.Button_Link
WHERE Machine_Links.Machine_Id = 6


Note that the tables are not separated by a comma. Separating with a comma is SQL-89 standard, and you run into major problem with cartesian products when missing JOIN predicates (like you are). The SQL-92 standard, where you use LEFT OUTER JOIN / RIGHT OUTER JOIN / INNER JOIN / CROSS JOIN and FULL JOIN forces predicates, so is better to use and more current (I'll let you google that).

Notice that I have made a LEFT OUTER JOIN from Machine_Links to QCBD_Files. This means that every row of Machine_Links will be returned, and if a row exists in QCBD_Files where QCBD_Files.Doc_Id=Machine_Links.Button_Link, then we'll get that information as well. Since it is LEFT OUTER JOIN, if QCBD_Files does not have a corresponding row, the row from Machine_Links will still be returned, but all the values for QCBD_Files for that row will be NULL. Also something to keep in mind is if there are multiple rows in QCBD_Files where QCBD_Files.Doc_Id=Machine_Links.Button_Link then multiple (potentially duplicate) rows will be returned in the query. I'm not sure how your database is structured, otherwise I could offer some help there. See how you go, that query may well fix your issue. Also, I would highly recommend doing some online study of basic SQL syntax to brush up your skills. Any tutorial that has text such as FROM table1, table2, ..., close that window straight away, they are trying to poison your brain with bad information :)/>

This post has been edited by e_i_pi: 11 April 2016 - 09:12 PM

Was This Post Helpful? 1
  • +
  • -

#3 SoundWaves   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 01-June 15

Re: Trouble with Case statement with select

Posted 12 April 2016 - 03:13 AM

Thanks for the lesson, sounds like I have some reading to do. This was my first attempt at a query like this, but am using multiple table queries in several spots in my application.

I am unable to use your query though, it tries to look for a match on every row. The DocId is an integer and the ButtonLink is either a matching integer or a file path. When it tries to compare the two it breaks the query. I may add another field to the table to store the foreign key of the document and keep them separate.

This post has been edited by andrewsw: 12 April 2016 - 03:24 AM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

#4 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: Trouble with Case statement with select

Posted 12 April 2016 - 04:02 PM

View PostSoundWaves, on 12 April 2016 - 09:13 PM, said:

I am unable to use your query though, it tries to look for a match on every row. The DocId is an integer and the ButtonLink is either a matching integer or a file path. When it tries to compare the two it breaks the query. I may add another field to the table to store the foreign key of the document and keep them separate.

It's best to store data types separately, so I would suggest moving the document ID to it's own nullable column. You can JOIN on integer to varchar, you just have to cast the integer to varchar, but as I said it would be best to keep integer data in an integer field and then foreign key constrain it
Was This Post Helpful? 1
  • +
  • -

#5 SoundWaves   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 01-June 15

Re: Trouble with Case statement with select

Posted 13 April 2016 - 03:16 AM

View Poste_i_pi, on 12 April 2016 - 04:02 PM, said:

View PostSoundWaves, on 12 April 2016 - 09:13 PM, said:

I am unable to use your query though, it tries to look for a match on every row. The DocId is an integer and the ButtonLink is either a matching integer or a file path. When it tries to compare the two it breaks the query. I may add another field to the table to store the foreign key of the document and keep them separate.

It's best to store data types separately, so I would suggest moving the document ID to it's own nullable column. You can JOIN on integer to varchar, you just have to cast the integer to varchar, but as I said it would be best to keep integer data in an integer field and then foreign key constrain it

Thanks, that's what I have done.
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6829
  • View blog
  • Posts: 28,311
  • Joined: 12-December 12

Re: Trouble with Case statement with select

Posted 13 April 2016 - 04:21 AM

Note that you don't have to quote the previous post in full, there is a Reply button further down the page, or use the Fast Reply box.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1