I have two tables and I am using SQL Server.  table1 structure is  Campiagn_Id  Campiagn_Name  1            test1 2            test2 3            test3   and table2 structure is   Campign_Id  EmalList 1           email_list_1 1           email_list_2 3           email_list_3 1           email_list_4 2           email_list_5 2           email_list_6 3           email_list_7 1           email_list_8   Now I want to fetch all the Campiagn_Id and Campiagn_Name from table1 and also all the respective EmalList from table2 in one query.  My Result will look like this  Campign_Id   Campiagn_Name    EmailList  1            test1            email_list_1,email_list_2,email_list_4,email_list_8 2            test2            email_list_5,email_list_6 3            test3            email_list_3,email_list_7   Or any other suggested output will be fine  I think my question is pretty much clear to all of you.
120659
Say I have the following query:  SELECT *  FROM AppDetails, AppTags  WHERE AppDetails.AppID = '1'    AND AppDetails.AppID = AppTags.AppID   Which gives the following results:  AppID    AppName        AppType    Tag 1        Application1   Utility    Test1 1        Application1   Utility    Test2 1        Application1   Utility    Test3   How can I modify the query to return something like this:  AppID    AppName        AppType    Tags 1        Application1   Utility    Test1,Test2,Test3
28038
Is there a graceful way to handle passing a list of ids as a parameter to a stored procedure?   For instance, I want departments 1, 2, 5, 7, 20 returned by my stored procedure. In the past, I have passed in a comma delimited list of ids, like the below code, but feel really dirty doing it.   SQL Server 2005 is my only applicable limitation I think.  create procedure getDepartments   @DepartmentIds varchar(max) as   declare @Sql varchar(max)        select @Sql = 'select [Name] from Department where DepartmentId in (' + @DepartmentIds + ')'   exec(@Sql)
95576
Supposing we have a table with four columns (a,b,c,d) of the same data type.  Is it possible to select all distinct values within the data in the columns and return them as a single column or do I have to create a function to achieve this?
107229
Lately, I have been trying to figure out the most generic query for getting tracking data into one table. As suggested in the , I have been using this join:  ... FROM _Sent s LEFT JOIN _Job as j ON s.JobID = j.JobID  LEFT JOIN _Open o ON s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1 LEFT JOIN _Click c ON s.JobID = c.JobID  and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1 LEFT JOIN _bounce b ON s.JobID = b.JobID and s.ListID = b.ListID and s.BatchID = b.BatchID and s.SubscriberID = b.SubscriberID and b.IsUnique = 1   My results show the same email send, with the same JobID, sent exactly at the same time, to the same subscriber, but with different batch numbers. On top of that, only the first record has an open date:    According to documentation, BatchID   is "The batch ID number for any batches used in the send" - but what does it mean, really? And how should the  BatchID be treated in queries?   Thanks!
93847
I have several hundred (currently 466, but ever growing) tables I have to copy from one server to another.  I have never had to do this before, so I'm not sure at all on how to approach it. All the tables are in the same format: Cart&lt;Eight character customer number&gt;  This is part of a larger project of which I am merging all these Cart&lt;Number&gt; tables to one Carts table, but that's a whole different question altogether.  Does anyone have a best-practice method I can use to copy all these tables over? The database names on both servers are the same, if that helps. And as I said earlier, I have the sa account so I can do whatever is necessary to get the data from A to B. Both servers are in the same server farm, as well.
46559
One of my columns is called from. I can't change the name because I didn't make it. Am I allowed to do something like SELECT from FROM TableName or is there a special syntax to avoid the SQL Server being confused?
29132
I'm looking at creating a basic ORM (purely for fun), and was wondering, is there a way to return the list of tables in a database and also the fields for every table?  Using this, I want to be able to loop through the result set (in C#) and then say for each table in the result set, do this (e.g. use reflection to make a class that will do or contain xyz).  Further to this, what are some good online blogs for SQL Server? I know this question is really about using system SPs and databases in Sql Server, and I am ok with general queries, so I'm interested in some blogs which cover this sort of functionality.  Thanks
6644
I've got two tables:  TableA ------ ID, Name  TableB ------ ID, SomeColumn, TableA_ID (FK for TableA)   The relationship is one row of TableA - many of TableB.  Now, I want to see a result like this:  ID     Name      SomeColumn  1.     ABC       X, Y, Z (these are three different rows) 2.     MNO       R, S   This won't work (multiple results in a subquery):  SELECT ID,        Name,         (SELECT SomeColumn FROM TableB WHERE F_ID=TableA.ID) FROM TableA   This is a trivial problem if I do the processing on the client side. But this will mean I will have to run X queries on every page, where X is the number of results of TableA.   Note that I can't simply do a GROUP BY or something similar, as it will return multiple results for rows of TableA.   I'm not sure if a UDF, utilizing COALESCE or something similar might work?
38248
I have three tables and i need an out put which has data from two tables, i would like to create the same using macro.  Table A  A B C D   Table B  Apple Orange Pears   Table C  Americas Asia Europe   The Output i need is  A Apple Americas A Apple Asia A Apple Europe A Orange Americas A Orange Asia A Orange Europe   So i guess for each letter from Table A, there would be 12 additional rows created. The above is the sample data and I have around 5000 rows in Table A.
85275
I have two tables with date and id fields. I want to join on both fields.  I tried   JOIN t2 ON CONCAT(t1.id, t1.date)=CONCAT(t2.id, t2.date)   that works, but it is very slow. is there a better way to do this?
15483
I'm experiencing an annoying problem with a table named SysMessages in one of our databases. When I run a select statement I don't get the contents of this table, but of the sys.sysmessages view in the master database. A use [DBNAME] doesn't help, nor select * from [DBNAME].dbo.SysMessages.  I'm in the middle of a migration to a newer SQL Server version. In SQL Server 2008 R2 I didn't encounter this problem with this database, but in SQL Server 2016 I'm not able to query my own SysMessages table.  Any help would be greatly appreciated.
14064
My database contains three tables called Object_Table, Data_Table and Link_Table. The link table just contains two columns, the identity of an object record and an identity of a data record.  I want to copy the data from DATA_TABLE where it is linked to one given object identity and insert corresponding records into Data_Table and Link_Table for a different given object identity.  I can do this by selecting into a table variable and the looping through doing two inserts for each iteration.  Is this the best way to do it?  Edit : I want to avoid a loop for two reason, the first is that I'm lazy and a loop/temp table requires more code, more code means more places to make a mistake and the second reason is a concern about performance.  I can copy all the data in one insert but how do get the link table to link to the new data records where each record has a new id?
24850
Using the simple example below, what is the best way to return results from multiple tables using Linq to SQL?  Say I have two tables:  Dogs:   Name, Age, BreedId Breeds: BreedId, BreedName   I want to return all dogs with their BreedName.  I should get all dogs using something like this with no problems:  public IQueryable&lt;Dog&gt; GetDogs() {     var db = new DogDataContext(ConnectString);     var result = from d in db.Dogs                  join b in db.Breeds on d.BreedId equals b.BreedId                  select d;     return result; }   But if I want dogs with breeds and try this I have problems:  public IQueryable&lt;Dog&gt; GetDogsWithBreedNames() {     var db = new DogDataContext(ConnectString);     var result = from d in db.Dogs                  join b in db.Breeds on d.BreedId equals b.BreedId                  select new                         {                             Name = d.Name,                             BreedName = b.BreedName                         };     return result; }   Now I realize that the compiler won't let me return a set of anonymous types since it's expecting Dogs, but is there a way to return this without having to create a custom type?  Or do I have to create my own class for DogsWithBreedNames and specify that type in the select?  Or is there another easier way?
12604
I have 2 tables. One (domains) has domain ids, and domain names (dom_id, dom_url).  the other contains actual data, 2 of which columns require a TO and FROM domain names. So I have 2 columns rev_dom_from and rev_dom_for, both of which store the domain name id, from the domains table.  Simple.  Now I need to actually display both domain names on the webpage. I know how to display one or the other, via the LEFT JOIN domains ON reviews.rev_dom_for = domains.dom_url query, and then you echo out the dom_url, which would echo out the domain name in the rev_dom_for column.  But how would I make it echo out the 2nd domain name, in the dom_rev_from column?
7766
This is a very basic query I can't figure out....  Let's say I have a two column table like this:  userid  |  roleid --------|--------    1    |    1    1    |    2    1    |    3    2    |    1   I want to get all distinct userids that have roleids 1, 2 AND 3.  Using the above example, the only result I want returned is userid 1.  How do I do this?
108057
What's the best way to extract the first word of a string in sql server query?
83332
I have two tables: TABLE1 and TABLE2.  And I am running this select query to fetch the data. It takes long time to run:  SELECT TABLE1.* , TABLE2.COL1 , TABLE2.COL4 FROM TABLE1, TABLE2      WHERE TABLE1.COL1 IN ('1','TEXT','HO TED')        AND TABLE1.COL2= 1800   AND TABLE2.COL1 = 10519198        AND TABLE1.COL3&gt;= 100   AND TABLE1.COL4= TABLE2.COL2   AND TABLE2.COL3=('TEXT')   How could I improve the performance of this select query? Does too many AND affect my performance?