Transact SQL :: Combine 3 Tables In One Search Result
Jul 1, 2015
I need a query to publish the front page of a blog. Each blog post needs to show BlogTitle, BlogText, PublishDate, PublishBy, Primary Image and number of comments. I would like to be able to do this in one sql statement, if possible.
The table structure is below, you can assume the first image returned from the image table is the primary image.
CREATE TABLE [dbo].[Blogs](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BlogTitle] [nvarchar](200) NULL,
[BlogText] [nvarchar](max) NULL,
[Tags] [nvarchar](200) NULL,
[Code] ....
View 3 Replies
ADVERTISEMENT
Aug 20, 2007
In Sql Server 2005 Express I have this table:CREATE TABLE [dbo].[Sections](
[SectionID] [int] NOT NULL,
[DocumentNo] [smallint] NULL,
[SequenceNo] [smallint] NULL,
[SectionNo] [smallint] NULL,
[DocumentTypeID] [smallint] NULL,
[SectionText] [ntext] NULL)
Each paragraph of text (SectionText) is in its own row
(SectionNo) Each primary document has a DocumentTypeID of 1 with
three subdocument types (2=Index, 3=Background, 4=Report).I run this query and return a collection of single rows from various documents grouped together by DocumentNo: SELECT *
FROM Sections
WHERE CONTAINS (SectionText, 'exercise')
ORDER BY DocumentNo
For each row that contains the search term, I would like to
return the full document (all rows as parapraphs within one row of
returned data). In other words, I want to reconstitute the full
document as it existed prior to being inserted into the database with
paragraph separation. For exampe, if the search term is in row 3
of DocumentNo=5, DocumentTypeID=2, I want to return all the rows of
that document in one block of text that retains paragraph format
(preferablly with a line break and carriage return between
paragraphs). How can this be done?
View 2 Replies
View Related
Jul 6, 2015
I have 4 table
TBLA
id
1
2
3
----
TBLB
ID COUNT
1 2
2 2
3 2
----
TBLC
ID COUNT
1 2
2 2
----
TBLD
ID COUNT
1 3
2 3
NEED TO GET OUT PUT LIKE BELOW
ID aCOUNT bCOUNT cCOUNT dCOUNT
1 2
2 3
2 2
2 2
3 2
View 2 Replies
View Related
Aug 18, 2015
I am tying to join tables to get the result but it is not showing any data,i have shipping address column in both tables I want to show data in single column I don't know how to display.
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress ,rs.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID
inner join
ReSaleorReStock1Table rs on r1.ProductID=rs.ProductID
where r1.HazMat='No' order by ProductID
If I join two tables it is showing data
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID
where r1.HazMat='No' order by ProductID
View 9 Replies
View Related
Jul 29, 2015
Is it possible to search a string/value across 1000's of tables and just display the table name and column name which it is in. I don't need to know every instance of the string/value only that I can find it in a given table name.
View 9 Replies
View Related
Jun 18, 2008
I have 3 sql queries:ex:select * from table 1 where id = 2select * from table 1 where name = 'name'select * from table 1 where date = 'date' I want to combine these three queries into one stored procedure.I am not sure how to do this.i want to display some column data from these 3 queries on 3 table rows as:<td> colum1 </td><td> colum2 </td><td> colum3 </td>so my SP should return some datatable .any suggestiions
View 3 Replies
View Related
Jun 2, 2008
This might be a question with an extremely easy answer.. I don't know but here I go.
I want a report with lets say
|A | B | C |
----------------
I can easily figure out the sql statements to find the columns A, B and C individually but how do I combine them?
so lets say I have
select cola as A from table1 where ....
select colb as B from table2...
They are not from the same table so I cannot combine them either (I cannot do select cola, colb from table1 etc.. )
How would I do this? Am I missing something?
View 5 Replies
View Related
Feb 20, 2008
I have a question regarding Execute SQL Task as I combined one statement like select count(*) from destination table, and insert into error table (source count, error count, destination count) values (?,?,?).
If I use two Execute SQL Task, it should work. I was wondering that is it possible to combine select and insert into one Execute SQL Task direct input. How to approch this?
View 6 Replies
View Related
Jan 9, 2014
I would like to pull all the columns from a table where the date column is within 6 months from the max date (i.e. Jul, Aug, Sep, Oct, Nov, & Dec). In addition to that, I would like to pull another column -the summary column - from the same table where the date = max(date) (Dec only).
I have written 2 queries and they produce the correct data. However, I don't know how to combine them into one resultant table. I tried to do a left join and had difficulties dealing with the different where statements from the 2 queries..
Here is query #1:
select investor, full_date, month_end_summary, category, loan_count
from cust_table
where datediff(month,full_date,(select max(full_date) from cust_table)) < 6
group by investor, full_date, month_end_summary, category, loan_count
order by investor, full_date
Here is query #2:
select investor, full_date, month_end_summary
from cust_table
where datediff(month,full_date,(select max(full_date) from cust_table)) =0
order by investor, full_date
Can they be combined into one query to produce one result table??
View 3 Replies
View Related
May 3, 2006
I want to call my stored proc for that last 4 months. Basically all I need to do is pass each month's first date and it will do the rest. Should I shove this into a UDF first? I'm not sure if I can do that. The struction is here behind my stored proc: http://www.webfound.net/storedproc.txt
EXEC IT_Get_Dashboard_Monthly '2006-05-03 12:03:43.910' <-- change to UDF or leave it? Then how can I loop and change each month to cover the last 4 months?
I also need to ensure all 4 values returned in each interation show up in one row in the final result set that is produced
View 1 Replies
View Related
Jul 15, 2015
I have two cube and i would like to get data from both cube and combine the results from both cubes to get final result to display result in SSRS reports like we can do in Stored procedure using temporary tables/Joins.Is there any way in SSAS to combine the data from multiple cubes? Data needs to be retrieved from the cubes based on the user inputs.
View 3 Replies
View Related
Aug 13, 2015
I have one table list this
declare @test table(Id int, Description varchar(500))
insert into @test
values (1, '<b>Name :</b> XUZ <br/><br/>'),
(1, '<b>Type : </b> QWE <br /><br/>'),
(2, '<b>Name : </b> ABC <br /><br/>'),
(2, '<b>Type : </b> FGH <br /><br/>')
My expected result is
1, '<b>Name :</b> XUZ <br/><br/> <b>Type : </b> QWE <br /><br/>'
2, '<b>Name : </b> ABC <br /><br/> <b>Type : </b> FGH <br /><br/>'
As you can see its combine two rows of data group by id. Issue is If I use for xml path('') then its replace my html tags <b> to like <b> so after that then I again i need to replace all these symbols. Is it possible to get expected result without using xml path or if we use xml path then still my html tag leave as it is.
View 5 Replies
View Related
Jun 12, 2015
How to combine two or more procedure to make a single query.
I tried like below:
EXEC PROC1
UNION
EXEC PROC2
UNION
EXEC PROC 3
Its throwing invalid syntax.
View 3 Replies
View Related
Oct 18, 2015
I have 2 tables, i need to take the max date from PAY and Combine in MEN
MEN
======
id | Fname
========
1 | AAA
2 | BBB
3 | CCC
PAY
===
id | Tdate
=========
1 | 01.01.2015
1 | 02.01.2015
1 | 03.01.2015
2 | 06.01.2015
3 | 09.01.2015
3 | 10.01.2015i
I need to show this:
id | Fname | Tdate
=============
1 | AAA | 03.01.2015
2 | BBB | 06.01.2015
3 | CCC | 10.01.2015
View 5 Replies
View Related
Oct 15, 2015
I'd like to get results from ZTest_Contract being my result set, and would like to combine the subquery (which gets the Max) into the primary view ZTest_Contract.
CREATE VIEW [dbo].[ZTest_Contract] AS
Select
M.CUSTNMBR,
M.ADRSCode,
M.Contract_number,
M.MaxWSCONTSQ,
M.Equipment_id,
[Code] ......
View 3 Replies
View Related
Jul 16, 2015
I have the table below and like to combine the rows to create a single link row in a new column. The rows should be combined based on the job number columns which is the same for the rows to be combined.
DECLARE @M31
( M31_SQL_ID INT
,JOB_NUMBER INT
,LINE_NUMBER INT
,WORKS_DESC VARCHAR)
[Code] ...
Output should be as below
219242
16/7/15 called tenant and she thought we would just fix for free - advised her I can get a quote how ever she may have to pay - she will call back
219245
16/7/15 called tnt said no report number. Said she will speak with her husband and call back with her decision and 16/07/15 the work order was sent to agent ...
View 3 Replies
View Related
Nov 19, 2015
I am wanting to create a query so that I can combine each of the found duplicates into one entry.
An example of this is:
Name |ID |Tag |Address |carNum
-------------------------------------------------------
Bob Barker |2054| 52377 |235 Some road |9874
Bill Gates |5630| 69471 |014 Washington Rd. |3700
Bob Barker |2054| 97011 |235 Some road |9874
Bob Barker |2054| 40019 |235 Some road |9874
Steve Jobs |8501| 73051 |100 Infinity St. |4901
John Doe |7149| 86740 |7105 Bull Rd. |9282
Bill Gates |5630| 55970 |014 Washington Rd. |3700
Tim Boons |6370| 60701 |852 Mnt. Creek Rd. |7059
In the example above, Bob Barker and Bill gates are both in the database more than once so I would like the output to be the following:
Bob Barker |2054|52377/97011/40019|235 Some road |9874
Bill Gates |5630|69471/55970 |014 Washington Rd.|3700
Steve Jobs |8501|73051 |100 Infinity St. |4901
John Doe |7149|86740 |7105 Bull Rd. |9282
Tim Boons |6370|60701 |852 Mnt. Creek Rd. |7059
Notice how Bob Barker & Bill Gates appends the tag row (the duplicated data) into one row instead of having multiple rows. This is because I do not want to have to check the previous ID and see if it matches the current id and append to the data.
View 2 Replies
View Related
Oct 29, 2015
I cannot seem to find the syntax to combine IN + CASE in a WHERE clause
WHERE
ses.BK_MS_SESSION <= '2015-03'
AND vis.CAT_DRAW_STATUS =
(CASE ses.BK_MS_SESSION
WHEN '2015-03' THEN vis.CAT_DRAW_STATUS
ELSE
CASE stat.BK_MS_VISIT_STATUS
WHEN 'T' THEN 'X'
ELSE vis.CAT_DRAW_STATUS
END
END
) IN ('D','R')
View 7 Replies
View Related
Sep 20, 2007
I have two tables in MS SQL 2000 that I need to combine into one. they will share 3 columns and the rest will stay the same. the existing tables are very large and I REALLY don't want to plug in all the data by hand...Any nifty tricks??? I found software but dont want to spend $$ on it.
View 5 Replies
View Related
Mar 5, 2005
Here is my dilemma, i'm trying to combine the results of two different tables. Both tables are very similar but the data returned must be sorted before they are combined because I'm only returning the top xx records based on a hits column.
Here is a sample of the two databases:
Table 1
ID - SONG - HITS
1 - tb1SONG 1 - 356
2 - tb1SONG 2 - 1459
3 - tb1SONG 3 - 278
4 - tb1SONG 4 - 965
5 - tb1SONG 5 - 124
Table 2
ID - tb2SONG - HITS
1 - tb2SONG 1 - 412
2 - tb2SONG 2 - 85
3 - tb2SONG 3 - 2035
4 - tb2SONG 4 - 693
5 - tb2SONG 5 - 745
I have tried the following union query which combines the two RS's then sorts the data:
SELECT Top 2 ID, Song, Hits FROM Table1
UNION SELECT Top 2 ID, Song, Hits from Table2
Which would return the first two records from each then sort them like this:
2 - tb1SONG 2 - 1459
1 - tb2SONG 1 - 412
1 - tb1SONG 1 - 356
2 - tb2SONG 2 - 85
I would like to sort based on the hits column then combine the RS producing this:
3 - tb2SONG 3 - 2035
2 - tb1SONG 2 - 1459
4 - tb1SONG 4 - 965
5 - tb2SONG 5 - 745
Any ideas or solutions will be greatly appreciated.
Thanks
View 2 Replies
View Related
Jan 31, 2008
Im trying to combine 2 unrelated tables to create a new table with all the data. Both tables have the same number of rows. if i have 2 tables with 1 column each and 5 rows in each column, eg.
tb1
c1:
1
2
3
4
5
tb2
c2:
a
b
c
d
e
if i run the query
SELECT tb1.[c1], tb2.[c2] INTO tb3 FROM tb1, tb2
i get
tb3:
c1: c2:
1 a
1 b
1 c
1 d
1 e
2 a
2 b
2 c
........
how do i get
tb3:
c1: c2:
1 a
2 b
3 c
4 d
5 e
thanks,
View 9 Replies
View Related
Dec 21, 2006
Dear Experts
Here is my code:
SqlConnectioncn = new SqlConnection(ConfigurationManager.ConnectionStrings["SimvipConnectionString"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("SP_SEARCH", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@TypeOfSim", Request.QueryString["NID"]));
cmd.Parameters.Add(new SqlParameter("@CategoryID", Request.QueryString["CID"]));
cmd.Parameters.Add(new SqlParameter("@Price", Request.QueryString["L"]));
cmd.Parameters.Add(new SqlParameter("@Price2", Request.QueryString["S"]));
cmd.Parameters.Add(new SqlParameter("@Title", Request.QueryString["keyword"]));
cmd.Connection.Open();
cmd.ExecuteNonQuery();
SqlDataAdapter sqlAdapter1 = new SqlDataAdapter();
sqlAdapter1.SelectCommand = cmd;
DataSet ds = new DataSet();
sqlAdapter1.Fill(ds, "ds");
CollectionPager1.DataSource = ds.Tables["ds"].DefaultView;
CollectionPager1.BindToControl = DataList1;
DataList1.DataSource = CollectionPager1.DataSourcePaged;
}
My problem is when there is no result(no item in database), the DataList1 will display the text "some text will display here" instead of empty page.
In FormView I know the option <EmptyDataTemplate>. But in the DataList I don't know how to do this. I am a beginner.
Can anybody show me how....Thanks a lots
View 1 Replies
View Related
Nov 2, 2003
I'm wanting to migrate an existing customer's database into a new products db. The previous contractor used seperate tables for each product type, where I chose to use one "products" table.
My challenge has been that the previous db uses attributes that aren't common across all products. Would it be best to do a products_attribute table? If so, how would I query the previous products db and seperate the information during an insert between "product A" and "product A attributes"?
Sample existing table:
ID, Name, Price, Weight, Attr1, Attr2, Attr3, Attr4, Attr5, Attr6
New table: Products
ID (auto), ProductName, Price, Weight
- Products_Attribute table
- ... ?
View 6 Replies
View Related
Apr 6, 2005
I am new to SQL Server development, but I use the automated features in Enterprise Manager a lot.
I have a table with a specific format already existing in a SQL Server 2000 database. This is generated once a day from a flat file received from an outside vendor. I am now receiving a similar flat file from another vendor which is nearly identical, but with two differences.
First, the new flat file is missing two columns (not critical data).
Next, there is one column that is out of order in comparison to the other flat file (aside from the 2 missing columns).
I need a generic example of how to remove specific records from a table and add these new ones (from the new flat file) through the SQL Server. My intention is to have a job run at a specific time through the SQL Server.
Any help is appreciated. If you know of a good tutorial or something out there, I would be more than happy to check it out. Thank you so much for your help!
View 3 Replies
View Related
Apr 11, 2008
If i have to get the statuses from four different tables but want to get first table disabled , second table active and also have statuses field from the 3rd and 4th table how would i do that?
ALM,
PV,
AD,
ACE
select * from all tbls
where alm.id = pv.id
and alm status = 'disabled'
and pv status = 'active'
I want to include the status field from 3rd and 4th table.
THanks
View 1 Replies
View Related
May 23, 2008
Hi,
I have 2 tables called Table A, Table B,
In Table A i am having Data1, Data2 like 2 datas in Column 1
In Table B i am having Data2, Data3 Like 2 datas in Column 1
Now want a output like
Data1,
Data2,
Data3
Please help me to get this....
Thank you,
Senthil
View 4 Replies
View Related
Oct 6, 2015
I have 2 tables. first table contains name and age and the second table contains class and roll num. I have a 3rd table which contains all these 4 columns. Now I want to fill the 3rd table with the 1st and 2nd table's data.
View 8 Replies
View Related
Oct 29, 2006
How to write a sql to combine the 4 tables into one without repetitive records? The 4 tables have exactly the same fields.
The tables do not have primary key. The fields to identiry the rows is name and dob. In the case the name and dob is same for two records, the one with latest date_created is selected.
Thanks
View 9 Replies
View Related
Dec 20, 2007
how to
how to combine 2 tables but like this
table 1
table Employee on work
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
---------------------------------------
table 2
table Employee on holiday
------------------------
empid start_date end_date shift
-----------------------------------------
12345678 11/04/2007 31/04/2007 10
98765432 01/04/2007 19/04/2007 10
------------------------------------------
how to create a view that show me and combine the 2 tables
all month from first day of the month until the end of the month like this
-----------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
12345678 11/04/2007 10
12345678 12/04/2007 10
12345678 13/04/2007 10
12345678 14/04/2007 10
.................................
.................................... ...................add the missing date until the end of the month
12345678 31/04/2007 10
98765432 01/04/2007 10
.................................... ...................add the missing date from the start of the month
98765432 02/04/2007 10
................................
..............................
98765432 16/04/2007 10
12345678 17/04/2007 10
98765432 18/04/2007 1
98765432 18/04/2007 10
98765432 19/04/2007 10
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
---------------------------------------
TNX
View 5 Replies
View Related
May 13, 2014
I am looking for a way to combine the following two tables to get a result set that would look like this:
1ProjectOrange 2014-05-08 orange
1update1
1update2
1update3
2ProjectRed 2014-05-09 red
[Code]....
View 6 Replies
View Related
Feb 18, 2015
I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:
Table A:
Table B:
Table C:
So what query do I need write to have table like below?
View 3 Replies
View Related
Oct 11, 2006
I got two tables from 2 different server A and B.
I do a OLEDB source [server A] with "select ID, currencyNo, exchangerate from table A"
I do another OLEDB [ server B] source with "select currencyNo, currencyName from table B"
i want to combine these two OLEDB sources with a resultset
"select ID, currencyNo, currencyName, exchangerate from table A , B
where A.currencyNo = B.CurrencyNo"
how do i do this in SSIS? sorry if i m a noob. I dun want to use linked servers. can someone help?
View 7 Replies
View Related
Nov 5, 1999
I'm trying to figure out why I am not getting any result set back from a search that includes non-alphanumeric
or non-printable characters. For instance, if I have a table with a 20 character name column with names with
beginning ranges from A-Z, why doesn't the following return any rows:
select * from table where name < CHAR(126).
In the ASCII character set, 126 is a tilde (~) which is numerically above the alphanumeric ranges 1-9,a-z, and
A-Z. Shouldn't all records that sort lower in the character range be included in the result set ?
I'm assuming this has something to do with the default collation sequence being used which somehow does
not include characters outside the alphanumeric range. Any ideas ?
View 4 Replies
View Related
