Skip to main content
N
neve_m
Known Participant
October 2, 2009
Question

sql help querying multiple database tables

  • October 2, 2009
  • 1 reply
  • 1107 views

I am joining a number of tables, mostly to retrieve descriptive information like names from related tables, and the simpler joins are working ok but where I am a little stuck is that I need to get project names from one database table if the value in a column called "type" is 1, and an entirely different table (in a different database) if the value in column "type" is 2. Not all records have an entry in the column, it is an optional column so there are nulls as well. I am a bit unsure of how to do it, I tried <CFIF> statements and also SQL case but couldn't get it to work. I was trying to use cfif to read the value in the column but it didn't seem to do it, I am not sure if I just got the syntax wrong or whether it can't be done this way. I really want to do this within the SQL query so that all the data is in one final table ready to display. I will also later need to count the number of 1s and 2s.

Can I do this with an if statement or is there another way? My code was along the lines of:
<cfif isNumeric("m.type") IS 2 >

This topic has been closed for replies.

1 reply

D
Dan_Bracuk
Inspiring
October 2, 2009

Are the two databases on the same server and are they the same type (MSSQL, Oracle, whatever)?  If so, you might be able to set it up as a single database query, as long as the proper permissions are in place.  Otherwise, Queries of Queries might be the answer.

N
neve_mAuthor
Known Participant
October 2, 2009

Yes, same server, same database type (mssql). I have up to now been using a different dsn for the second database and just grabbing the data from the second database in the CF code when displaying the data another part of the app (Query of Queries used there), but it would be nice to have one neat table to use in multiple pages without post-processing. I want to do it the simplest possible way so I can reuse it. I was hoping it could be done in the sql with an if statement or something, but I haven't worked it out for the table in the same database let alone the one in the second database.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices