Transact SQL :: Join Two Tables With Multiple Rows?

Aug 13, 2015

I have to join two tables and i need to fetch All records from @tab2 and only max date record from @tab1 that ID is present in Tab2

1.) @Tab1 have multiple records for each ID

2.) @Tab2 also have multiple records for each ID

3.) Kind of Lef Outer join those tables with ID and take all records from @tab2 and only Max of date from @tab1 and order by ID and Date

Note: @Tab1 always have lesser dates than @tab2 for each ID

Tables looks like as follows 

declare @tab1 table (id varchar(3), effDt Date, rate int)
insert into @tab1 values ('101','2013-12-01',5)
insert into @tab1 values ('101','2013-12-02',2)
insert into @tab1 values ('101','2013-12-03',52)

[code]....

In the given ex, ID 103 should not come as it is not present in @tab2, ID 104 should come even it is not present in @tab1 as we ahve to use left outer join Result should like follows.

View 3 Replies


ADVERTISEMENT

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

I have a table with single row like below

 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0    | Value1    | Value2    | Value3    |  Value4  |

Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below

_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _

View 6 Replies View Related

Transact SQL :: To Show Multiple Column In Multiple Rows

Aug 14, 2015

I have the following  database structure

Stock        Depth41     Depth12    Depth34
AAA            1              2              1
BBB             2            2               4

How can I show  Each Depth column as seperate row

AAA          1
AAA          2
AAA          1  as follows

View 3 Replies View Related

Transact SQL :: Filter - Join Query Rows

Nov 14, 2015

Please refer to the below query. I want to filter inner join rows based on outer query column value (refer to bold text).

SELECT M.MouldId, SI.StockCode, MI.Cavity, MI.ShotCounter, CQ.SumOfCastedQty  as CastedQty, MI.CounterStartup 
FROM        MouldItem MI
JOIN (SELECT JD.MouldId, JC.StockCode, SUM(JS.CastedQty) AS SumOfCastedQty
FROM JobCasting AS JS INNER JOIN JobCreationDet AS JD ON JS.JobDetId = JD.Uniid INNER JOIN JobCreation AS JC ON JD.JobIdx = JC.Uniid

[Code] ....

View 2 Replies View Related

Transact SQL :: Left Join Returns Too Many Rows Vertically

Jun 18, 2015

I have a query that based 2 tables. I wrote a query with a left join on the base table but the result set returns multiple rows for each occurrence in the second table because of the left join. I want but to return all records  from on table A and only matching records from table B which id but I would wan tit to keep return them vertically as the because it make it difficult to read when put in a spreadsheet. It want it to return the values horizontally so the rows are not increasing for each occurrence on table b.

View 5 Replies View Related

JOIN Multiple Tables From Multiple Databases

May 23, 2008

Hello,
I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database.  My question is can you JOIN multiple tables in an SQL Statement from multiple databases.  Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables.  Can I link say tblRegister from Convention to tblUser in Services?
Thanks

View 3 Replies View Related

Insert Single Row / Multiple Rows Into Multiple Tables

Sep 3, 2014

How to insert single row/multiple rows into multiple tables by using single insert statement.

View 1 Replies View Related

Transact SQL :: How To Join 2 Tables Have Different Foreign Key

Apr 23, 2015

i have two tables Table one have 2 columns id and value
     
id value

1 Dell

2 Hp

B2 Hp-mini
B3 Hp-lapTop

3 Acer

the second table have 3 clomuns id,name,idTable

1 TeaBou B
2 Mark B
3 Jack 1
4 Piere 2
5 Jean 2
6 Mark 3

i tried this query

select*from table1 t1 ,table2 t2
where t1.id=t2.idTable

i had some data but also i need to include the person who have the B product.

View 6 Replies View Related

Inner Join Returns Multiple Duplicated Rows

Dec 3, 2013

Here is my query which returns multiple rows

SELECT
R.name, R.age,R.DOB,
ISNULL(D.Doc1,'NA') AS doc1,
ISNULL(C.Doc2,'NA') AS doc2
FROM
REQ R
inner join RES S ON R.Request_Id=S.Request_Id
inner join RES1 D ON D.Response_Id=S.Response_Id
inner join REQ1 C ON C.Request_Id=R.Request_Id

select * from RES1 where Response_Id = 111 -- return 3
select * from REQ1 where Request_Id = 222 --- returns 2

So at last inner join retuns 3*2 = 6 records , which is wrong here and i want to show 3 records in doc1 row and 2 records in doc 2 rows ...

View 5 Replies View Related

Transact SQL :: Get Result On Two Table Join With Multiple Parameter?

Jun 1, 2015

I have a criteria where i want to join table 1 with table 2 , table 1 consists of products which were given to salesman to sell and table 2 has the sales data which salesman has sold out. Now i want to know left over products of each sales with join .Below is my data, here is what i am trying to do, but it return only salesman 1 data.

CREATE TABLE Salesman_Product
(
SalesManID int,
ProductID int
)
INSERT INTO Salesman_Product (SalesManID,ProductID) Values (1,1),(1,2),(1,3),(1,4)  
INSERT INTO Salesman_Product (SalesManID,ProductID) Values (2,1),(2,2),(2,3),(2,4) 

[code]....

View 6 Replies View Related

Join 3 Tables Getting Rows Repeating

Jan 27, 2014

I am using three tables/view.

The first is a vendor table where I am pulling company/ID/name

I am using a left join to the other two tables on company/ID

The problem I am having is if the second table has 8 rows and the third table just has 1 row it will repeat 8 times.

How do I show all 8 from the second table but only the 1 from the third table?

You can see below that the voucher number and amount repeat

vendor_idvendor_namepo_nopo_amountvoucher_no amount_due
36999VITEK6012838 $174.00 2622666 $(1,791.00)
36999VITEK6016464 $822.90 2622666 $(1,791.00)
36999VITEK6017791 $876.00 2622666 $(1,791.00)
36999VITEK6025495 $600.00 2622666 $(1,791.00)
36999VITEK6029781 $930.00 2622666 $(1,791.00)
36999VITEK6034433 $3,264.00 2622666 $(1,791.00)
36999VITEK6037821 $2,715.00 2622666 $(1,791.00)

View 1 Replies View Related

Transact SQL :: Filtering Join Tables By Date

Sep 14, 2015

I need to Filter Employees by Available Date and grab their Basic contact information

I used Join to Join both tables  but I can't seem to find a way to only get data from employees where DateAv > SelectedDate.

I'm using this in a SqlCommand with C#. 

SELECT " EmployeeNameTable.ID, EmployeeNameTable.FullName, EmployeeNameTable.DateAV,ContactTable.HomePhone, ContactTable.MobilePhone, ContactTable.Email FROM EmployeeNameTable INNER JOIN ContactTable On EmployeeNameTable.ID = ContactTable.ID OrderBy EmployeeNameTable.FullName"

This code loads all employees Successfully but it doesn't filter them by the DateAV Column which is in Date Format.

View 2 Replies View Related

Transact SQL :: SUM Of Multiple Rows With Where And Group By

Apr 30, 2015

I have a table Transaction that looks something like the following :

TransactionID          

Currency        Credit             Debit
1     USD      500               0
2     Afcu          6000              0

[Code] ....

I write query like this

select SUM(credit)-SUM(Debit)as [Balance] ,Source from Transaction group by Source

And it came like

Balance Source           
1500                        USD
6000                        Afcu
6800                        INR
7000                        Pfc
-200                         AUD

But I also want to add  Afcu , Pfc with USD and  want output like

Balance Source
14500                        USD
6800                          INR
-200                           AUD

View 3 Replies View Related

Union Join To Return Multiple Rows Into Columns.

Feb 19, 2008

I have a subscriptions table that has many line items for each record. Each line item has a different type, dues, vol, Chapt.

101 dues Mem 100
101 Vol charity 200
101 chapt CHi 300

I want my end result to have one line item per record id, but I keep coming up with an error. I am pretty sure I am close, but need assistance before I can proceed.

101 mem 100 charity 200 chi 300

Error:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'PRODUCT_CODE'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'product_code'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'product_code'.



SELECTp.ID,
p.PRODUCT_CODE as Chapt,
p.product_code as Dues,
p.product_code as Vol
from (
SELECT ID,
product_code as Chapt,
Null as dues,
Null as Vol
from subscriptions
where prod_type = 'chapt'
AND BALANCE > 0

union all

SELECT ID,
Null as chapt,
product_code as Dues,
Null as vol
from subscriptions
where prod_type = 'dues'
AND BALANCE > 0

union all

SELECT ID,
Null as chapt,
Null as dues,
product_code as Vol
from subscriptions
where prod_type = 'vol'
AND BALANCE > 0

) AS p
GROUP BY p.id

View 9 Replies View Related

Returning Average Of Multiple Rows In A Table Join

Jul 23, 2005

Hi,I'm am looking for a little help. I need to create a SQL view whichjoins a few tables, and I need to return an average for a particularcolumn where a second duplicate ID exists...Heres an example of how the results could be returned...ID | Name | Order No. | Value---+------+-----------+---------5 | test | 1234 | 35 | test2| 1234 | 45 | test3| 1234 | 35 | void | 1235 | 55 | void2| 1235 | 65 | void3| 1235 | 55 | void4| 1235 | 7ID is my main join which joins the tablesName is a unique nameOrder No is the same for the different names, I only need to return onerow with this order no, and the first name (the rest are irrelevant)Value is the field which I wish to return as an average of all 3, 4 orhowever many rows is returned and share the same order no. This iswhere I get totally lost as I am pretty new to SQL. Can anyone provideany help on how I would go about limiting this query to the uniqueorder no's and returning the average of the value field, and I can takeit from there with my own tables.Thanks for your helpstr8

View 3 Replies View Related

How To Join Multiple Tables

Nov 13, 2011

When I run:

select scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no,
scheme.opheadm.customer, qty_received
from scheme.opheadm join scheme.porecpm on (ltrim(rtrim(scheme.porecpm.commnt)) like (ltrim(rtrim(scheme.opheadm.order_no)) + '/%'))
where
effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'

It gives me the 5 rows that I need to work with, one column is customer (which is giving me customer code) that I want to replace with customer name from another table

So I tried:

select scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no,
scheme.jcmastm.name, qty_received
from scheme.opheadm
join scheme.porecpm on (ltrim(rtrim(scheme.porecpm.commnt)) like (ltrim(rtrim(scheme.opheadm.order_no)) + '/%'))
join scheme.jcmastm on scheme.opheadm.customer = scheme.opheadm.customer
here
effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'

this works with the same 5 rows that i need but loops them through every customer from the table scheme.jcmastm giving me a total of 960 rows not just the 5 that i want to work with. why this is looping?

View 1 Replies View Related

How To Join Multiple Tables

Feb 6, 2007

hi i user this join and i have the answer like this"

select u.userid,
u.user_name,
u.password,
c.code_description as role_code,
convert(varchar, u.expiry_date,101) as expiry_date,
u.created_date,
u.active
from [usermaster] u inner join [codeMaster] c
on 'SP'=c.code
where u.userid = '2'

result:
userid user_name password role_code expiry_date
2billgatesbill Supervisor02/06/2007

created_date active
2007-02-06 00:00:00.000 0

so i have to join one more table which has the following records;

select * from HRUser_developerlog
result:
insserted_id user_date table operation userid
101/24/2007 11:47:54 AM usermasterinsert1
11/24/2007 1:02:18 PM usermasterinsert1
111/25/2007 9:26:12 AM usermasterinsert1
122/5/2007 9:56:48 AM usermasterupdate1
122/5/2007 10:23:01 AM usermasterinsert1
122/5/2007 10:23:38 AM usermasterupdate1
122/5/2007 4:10:11 PM usermasterupdate1
22/6/2007 8:53:37 AM usermasterinsert1
22/6/2007 9:48:24 AM usermasterdelete1

so i need to take the user_date using inserted_id and operation so i need the output as follows:

userid user_name password role_code expiry_date user_date
2billgatesbill Supervisor02/06/2007 2/6/2007 9:48:24 AM

for this i tried the following query:

select u.userid,
u.user_name,
u.password,
c.code_description as role_code,
convert(varchar, u.expiry_date,101) as expiry_date,
u.created_date,
u.active,
v.user_date
from [usermaster] u inner join [codeMaster] c inner join [HRUser_developerlog] v
on 'SP'=c.code or u.userid=v.inserted_id and v.operation='delete'
where u.userid = '2'

but i am getting error.can any onre please help me and please give me query please

View 2 Replies View Related

Multiple Join Between Two Tables

Aug 4, 2006

Hi,

I have two tables, let's say "Main" and "Dictionary".

The Main table has several fields that point to records in the same dictionary table. Because of the multiple joins I couldn't get any results if I use an expression like:

SELECT Main.ID, Dictionary.Text AS Data1, Dictionary.Text AS Data2

FROM Main, Dictionary

WHERE Main.Data1 = Dictionary.ID AND Main.Data2 = Dictionary.ID

What kind of join expression should I use? I have to generate this expression programmatically, so it's quite important to keep it as simple as possible!

Thx!

Örs









View 6 Replies View Related

Join Multiple Tables

Oct 13, 2006

I'm trying to join 3 tables:

EMPLOYEE - empid

SKILL - empid, skillid, skill

SKILLOPTIONS - skillid, option

An EMPLOYEE will always have at least 1 SKILL but each SKILL may or may not have any SKILLOPTIONS. I do an INNER JOIN:

EMPLOYEE->SKILL->SKILLOPTIONS but I only get a record if there is actually a SKILLOPTION. I want a record with EMPLOYEE and SKILL even if there are no SKILLOPTIONS. In Oracle it is the (+) symbol in the WHERE statement in conjunction with the JOIN. Am new to this so I'm sure the answer is simple.

View 2 Replies View Related

Transact SQL :: Join Tables To Get Result - No Data Showing

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

Transact SQL :: How To Break Rows Into Multiple Columns

Jul 14, 2015

I have a table with 100 rows, 1 field (ID), and I would like to write a query to output it as 4 rows, and 25 columns.

Row data
ID
1
2
3
4
5
6
7
8
9
...
98
99
100

Output will be like
c1  c2   c3    c4    c5....
1    5    9      13
2    6    10    14
3    7    11    15
4    8    12    16

View 4 Replies View Related

Transact SQL :: Combine Multiple Rows In A New Column

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

Transact SQL :: Merge Multiple Rows Onto A Single Row

May 13, 2015

I have a set of data which contains individual logon and logoff data. The table is as follows:

AGENTID, EVENTTIME, CURRENTSTATE
1234,       2015-05-12,    15 (For Logon) or 25 (For Logoff)

I'm hoping to extract this data as follows:

AGENTID, LOGON DATE/TIME, LOGOFF DATE/TIME, DURATION

View 19 Replies View Related

DB Engine :: How To Solve Multiple Rows Result From Inner Join Query

Dec 3, 2015

I have 3 tables:
 
TABLE [dbo].[Tbl_Products](
[Product_ID] [int] IDENTITY(1,1) NOT NULL,
[Product_Name] [nvarchar](50) NOT NULL,
[Catagory_ID] [int] NOT NULL,
[Entry_Date] [date] NOT NULL,

[Code] ....

I am using this query to get ( Product name from tbl_products , Buy Price - Total Price- Total Quantity from Tbl_Details )

But am getting a multiple result if the order purchase has more than 1 item :

SELECT DISTINCT B.Product_Name,A.AllPieceBoxes,
A.BuyPrice,A.TotalPrice,A.BuyPrice
FROM
Tbl_Products B INNER JOIN Tbl_PurchaseHeader C
ON C.ProductId=B.Product_ID INNER JOIN Tbl_PurchaseDetails A
ON A.PurchaseOrder=C.purchaseOrder
WHERE A.PurchaseOrder=3

View 5 Replies View Related

Selecting Rows From Two Tables With JOIN And Ordering Problem

Dec 8, 2007



Hi,

First the environment: two tables A and B.

Table A: ID (unique-identifier)
Table B: ID_A (unique-identifier to A.ID, relation)

DTime (datetime)

Rows (id1 and id2 are Id examples):
A: id1
id2
B: id1 and 12:00:00 (date not important)
id1 and 13:00:00
id2 and 12:00:00

Example:
SELECT A.ID, B.DTIME
FROM A
LEFT JOIN B ON B.ID_A = A.ID
WHERE B.DTime < '14:00:00'
ORDER BY NEWID()

When I run this, I get the three rows of table B. But what I want is to get each table A row once, and get the nearest datetime of WHERE expression from the relation of table B.
So, the result must been two rows, id1 and id2, and id1 with '13:00:00' row because this is the nearest value of '14:00:00'.

How can I do this? DISTINCT trying by A.ID of SELECT, but doesn't work. Also ORDER BY B.DTime will work, but not random by NEWID() anymore.

Thank you.

View 3 Replies View Related

Join And Pivot Multiple Tables?

Jul 24, 2012

I have three tables, Users, DocType and Docs. In the DocType table there are multiple entries for allowed document types, the descriptions and other pertinent data. In the Docs table, there are all manner of documents. In the User table are the users.

The DocType and Docs tables are relational. DocType.ID = Docs.tID
The Users and Docs tables are relational. Users.ID = Docs.uID

Every user is allowed to have exactly one document of each type. Therefore if there are 10 document types in the DocType table, there may be as many as 10 matching documents in the Docs table.

What I need is a single record for each user returning a boolean for each document type, whether or not there is a matching record in the Docs table.

For example, there are 5 document types defined in the DocType table (types 1 - 5), so the DocType table has 5 rows. In the Docs table, there are 23 rows, and in the User table there are 10 rows. Given that each user may have only one of each DocType, there could be a maximum of 50 rows in the Docs table, but there are 23, meaning that on the average each user is missing one document.Now the challenge is to return a table of all the users (10 rows) with a boolean value for each of the rows in DocType (as columns) based on whether there is a value in the Docs table that matches both the DocType and User.

View 2 Replies View Related

SQL 2012 :: Join Multiple Tables

May 12, 2014

I have 3 tables , Customer , Sales Cost Charge and Sales Price , i have join the customer table to the sales price table with a left outer join into a new table.

i now need to join the data in the new table to sales cost charge. However please note that there is data that is in the sales price table that is not in the sales cost charge table and there is data in the sales cost charge table that is not in the sales price table ,but i need to get all the data. e.g. if on our application it shows 15 records , the sales price table will maybe have 7 records and the sales cost charge table will have 8 which makes it 15 records

I am struggling to match the records , i have also tried a left outer join to the sales cost charge table however i only get the 7 records which is in the sales price table. see code below

SELECT
a.[No_],
a.[Name],
a.[Currency Code],
a.[Salesperson Code],
b.[Sales Code],

[code]....

View 4 Replies View Related

Join Tables On Multiple Criteria

Oct 14, 2013

I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.

Here is table a:

naics ust_code port all_qty_1_yr all_qty_2_yr all_val_yr all_air_val_yr all_air_wgt_yr all_ves_val_yr all_ves_wgt_yr all_cnt_val_yr all_cnt_wgt_yr all_border_val_yr
11111000 2010 2002 8070569.14298579 0 2335641254.30021 0 0 2335641254.30021 8156408492.66667 0 0 0
11111000 2230 2010 280841.478063446 0 84622385.9133129 0 0 84622385.9133129 299600780.773355 0 0 0
11111000 2410 1401 25735 0 12305667 0 0 12305667 25719794 0 0 0

[Code] ....

and here is table b:

naics ust_code port stat_month Cum_qty_1_mo Cum_qty_2_mo Cum_all_val_mo Cum_air_val_mo Cum_air_wgt_mo Cum_ves_val_mo
11111000 1220 0106 01 2 0 3440 0 0 0
11111000 1220 0107 03 14 0 3442 0 0 0
11111000 1220 0108 09 0 0 0 0 0 0

[Code] ....

I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’

/****** Script for SelectTopNRows command from SSMS ******/
SELECT a.[naics]
,a.[ust_code]
,a.[port]
,a.[all_qty_1_yr]
,a.[all_qty_2_yr]

[Code] ....

Output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like

* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)

View 1 Replies View Related

Transact SQL :: Date Value Between One Of The Calendar Periods In Multiple Rows?

Aug 20, 2015

Question: How to determine if a date value was between one of the date periods that appear in multiple rows?

Background: We have a table of "license valid" periods, wherein each license can have one or more rows.  (As you know, a driver's license can be started, expired, renewed, suspended, reinstated, revoked, etc.)  Instead of of having a license activity table--from which valid license periods could be extrapolated--we store just the periods for which a license was valid.

My task is to take a list of licenses and specific dates and determine if each license was valid as of that date, returning either true or false.  What is the best way to accomplish this?

DECLARE @ValidityInQuestion TABLE (
LicenseID int
, DateValidityInQuestion date);

DECLARE @LicenseValidPeriods TABLE (
LicenseID int
, BeginDate date
, EndDate date);

[Code] ...

 How then do I query both tables in order to get the same result that results from the following query?

SELECT
12345 AS LicenseID
, '2015-01-15' AS DateValidityInQuestion
, 1 AS LicenseActive
UNION
SELECT
67890
, '2015-02-04'
, 0;

I assume I need to join on the LicenseID columns, but my understanding stops there.

View 4 Replies View Related

Transact SQL :: String Manipulation Single To Multiple Rows

Nov 7, 2015

The recipe preparation instructions are stored in a table by RecipeID.  The prep instructions are in a single VARCHAR(MAX) column and look something like this:  

1.  Boil Water 
2.  Add noodles 
3.  Add cheese sauce 
4.  Stir well

Now they want this single VARCHAR(Max) column broken into 2 columns - Step and Prep Instruction like this: Boil WaterAdd noodlesAdd cheese sauceStir well.I figure I can use the appearance of a number followed by a period and a space to determine the existence of a new row.  How would I accomplish this in T-SQL?

View 11 Replies View Related

Transact SQL :: Converting From Multiple Rows With Single Values To Single Rows With Multiple Values

May 10, 2015

Here is some data that will explain what I want to do:

Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456

Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456

View 4 Replies View Related

Nested Join To Return Only Rows With Null Values From All Tables

Oct 17, 2007



Hello,

I have this INNER JOIN that is fine to show all possible combinations. But I need to show only rows that have one or more Null values in tbIntersect.

Should I use nested LEFT JOINT? How?

This is the SQL statement:
sSQL = "SELECT DISTINCT tbCar100.Car100_ID, tbCar100.Description100 AS [Caractéristique 100], " & _
"tbCar200.Car200_ID, tbCar200.Description200 AS [Caractéristique 200], " & _
"tbCar300.Car300_ID, tbCar300.Description300 AS [Caractéristique 300], " & _
"tbCar400.Car400_ID, tbCar400.Description400 AS [Caractéristique 400], " & _
"tbCar500.Car500_ID, tbCar500.Description500 AS [Caractéristique 500], " & _
"tbCar600.Car600_ID, tbCar600.Description600 AS [Caractéristique 600], " & _
"tbCar700.Car700_ID, tbCar700.Description700 AS [Caractéristique 700], " & _
"tbProducts.Prod_ID, tbProducts.PartNumber AS [Part Number] , tbProducts.Description AS [Description] , tbProducts.DateAdded AS [Date] " & _
"FROM tbProducts INNER JOIN (tbCar700 INNER JOIN (tbCar600 INNER JOIN (tbCar500 INNER JOIN (tbCar400 INNER JOIN (tbCar300 INNER JOIN (tbCar100 INNER JOIN " & _
"(tbCar200 INNER JOIN tbIntersect ON tbCar200.Car200_ID = tbIntersect.Car200_ID) " & _
"ON tbCar100.Car100_ID = tbIntersect.Car100_ID) ON tbCar300.Car300_ID = tbIntersect.Car300_ID) ON tbCar400.Car400_ID = tbIntersect.Car400_ID) ON tbCar500.Car500_ID = tbIntersect.Car500_ID) ON tbCar600.Car600_ID = tbIntersect.Car600_ID) ON tbCar700.Car700_ID = tbIntersect.Car700_ID) ON tbProducts.Prod_ID = tbIntersect.Prod_ID " & _
";"


Here is the content of the tbIntersect table:
Car100_ID Car200_ID Car300_ID Car400_ID Car500_ID Car600_ID Car700_ID Prod_ID ID
1 1 1 1 1 1 1 1 1
1 2 1 1 1 1 1 19
1 3 1 1 1 1 1 20


I need to return the rows that have null data, ex: second row because Prod_ID is NULL and third row because Car300_ID is NULL. In fact I need the data from the other joint tables that correspond to these ID fields.

Thanks

View 5 Replies View Related

Need Help Creating Outer Join On Multiple Tables

Nov 2, 2005

I'm trying to join 3 tables in an outer join since I am loosing records that need to be included if I only use an inner join. I am pulling data from an MSDE database using the microsoft query tool.

The problem is that I get the message that I can't use an outer join on a query with more than 2 tables, but that can't be right can it?

I'm a SQL code novice so any help would be greatly appreciated!

SELECT
Article.articleId
, Article.articleName
, Article.articleStatus
, Articlegroup_2.ArticlegroupId
, Articlegroup_2.g2_key
, Articlegroup_2.g2_name
, articleGroup.articleGroupId
FROM
HIP.dbo.Article Article, HIP.dbo.articleGroup articleGroup, HIP.dbo.Articlegroup_2 Articlegroup_2
WHERE
articleGroup.articleGroupId = Article.articleGroupId AND
Article.articleGroupId2 = Articlegroup_2.Articlegroup_2_Id

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved