[SQL Server] Case statements advice - query case field? |
- Case statements advice - query case field?
- Joining data from two tables to another
- Using Replace to ..well...replace...
| Case statements advice - query case field? Posted: 12 Aug 2013 01:25 AM PDT Hi,So basically I have three tables with three types of "things". Each of these tables have a column called "name". My fourth table contains references to these tables through an id field, only one of these tables will be related to the fourth table - I have produced the following SQL so the query has a name column which is populated by the related tables name.[code="sql"]SELECTCASE WHEN thing1.name IS NOT NULL THEN thing1.name WHEN thing2.name IS NOT NULL THEN thing2.name WHEN thing3.name IS NOT NULL THEN thing3.name END AS name,base.ls_id, base.date_lease_start, base.date_lease_end, base.lease_term, base.amount_current_rentFROM base_table AS base LEFT OUTER JOIN thing1_table AS thing1 ON base.as_id = thing1.as_id LEFT OUTER JOIN thing2_table AS thing2 ON base.bl_id = thing2.bl_id LEFT OUTER JOIN thing3_table AS thing3 ON base.ld_id = thing3.ld_id[/code]That query works, my problem is the following, I hope to use the query in a SSRS report with a parameter enabling a search against the name column (which the case statement populates).I would like to query the returned "name" column from that query - I could populate a temporary table however I would like to know what the "proper" way is for this? |
| Joining data from two tables to another Posted: 12 Aug 2013 05:55 AM PDT Hi All;. I am testing some code with temporary tables. I have let's say table 1 which I will call "mother table". Here what it looks like.ID Letter Data11 A 23 2 B 24 3 C 25 4 D 12 5 E 14 6 F 10 7 G 12 I am trying to join data from two tables which I will call "child1" and "child2" to the mother table on matches from letter column to have something like this. ID Letter Data1 LetterC DataC1 A 23 A 1232 B 24 B 1243 C 25 C 125 4 D 12 D 1275 E 14 E 1286 F 10 F 1297 G 12 G 130Here is "Child1" and "Child2" respectivelyID Letter DataC21 D 127 2 E 128 3 F 129 4 G 130 ID Letter DataC11 A 123 2 B 124 3 C 125 I have tried for one hour to come up with some code, but it seems that I am only able to get only data from one table joined to the mother table with the following query. I'm stuck and was wondering if any suggestion or code for a poor soul like me. Here is what I have so far. thanksUSE TEMPDBIF exists (SELECT * FROM Mother AS M inner join dbo.cHILD1 AS C1 ON M.Letter = C1.LETTER)begin SELECT M.LETTER, C1.LETTER, M.DATA1, C1.DATAC1 FROM Mother as M inner join child1 as c1 on c1.letter = m.Letterendelsebegin select M.Letter , C2.Letter, M.data1, C2.DataC2 from Mother as m inner join child2 as c2 on c2.letter = m.Letterend |
| Using Replace to ..well...replace... Posted: 12 Aug 2013 12:41 AM PDT I have a field in a table which has hard coded paths for files. Like "http://www..../downloads/files/dlr-tlk-apr-2001.pdf"I need to update all references to "downloads' and change it to "forms" so all of the paths would look like:"http://www......./forms/files/dlr-tlk-apr-2001.pdf"I assume I need to use Replace. What would be the best way of doing this? |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
