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


ADVERTISEMENT

Return NULL Values In SELECT Statement With INNER JOIN ?

May 16, 2005

If I try to run the code below, and even one of the values in the INNER
JOIN statements is NULL, the DataReader ends up with zero rows. 
What I need is to see the results even if one or more of INNER JOIN
statements has a NULL value.  For example, if I want info on
asset# 2104, and there's no value in the DriverID field, I need the
rest of the data to display and just have the lblDriverName by
blank.  Is that possible?

<code>
    Sub BindSearchGrid()
        Dim searchUnitID As String
        Dim searchQuery As String
        searchUnitID = tbSearchUnitID.Text
        lblIDNum.Text = searchUnitID
        searchQuery = "SELECT * FROM Assets " & _
        "INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _
        "INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _
        "INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _
        "INNER JOIN AssetCategories
ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _
        "INNER JOIN Store ON
Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '"
& searchUnitID & "'"

        Dim myReader As SqlDataReader
        myReader = Data.queryDB(searchQuery)
        While myReader.Read
            If
Not IsDBNull(myReader("Store Name")) Then lblStrID.Text =
myReader("Store Name")
            If
Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text =
myReader("AssetCategory")
            If
Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text =
myReader("Condition Description")
            If
Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text =
myReader("DepartmentName")
            If
Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit
ID")
            If
Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year")
            If
Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make")
            If
Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model")
            If
Not IsDBNull(myReader("Mileage")) Then lblMile.Text =
myReader("Mileage")
            If
Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text =
myReader("Vin Number")
            If
Not IsDBNull(myReader("License Number")) Then lblLicNum.Text =
myReader("License Number")
            If
Not IsDBNull(myReader("Name")) Then lblDriverName.Text =
myReader("Name")
            If
Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text =
myReader("DateAcquired")
            If
Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text =
myReader("DateSold")
            If
Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text =
myReader("PurchasePrice")
            If
Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text =
myReader("NextSchedMaint")
            If
Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR")
            If
Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW")
            If
Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text =
myReader("Crane Capacity")
            If
Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text =
myReader("Crane Certification")
            If
Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text =
myReader("Repair Cost")
            If
Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text =
myReader("Estimate Replacement")
            If
Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text =
myReader("SalvageValue")
            If
Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text =
myReader("CurrentValue")
            If
Not IsDBNull(myReader("Comments")) Then lblCom.Text =
myReader("Comments")
            If
Not IsDBNull(myReader("Description")) Then lblDesc.Text =
myReader("Description")

        End While
    End Sub</code>

View 1 Replies View Related

How To Return Null Values For Non-exist Record On A Left Join Statement

Oct 16, 2004

I have table Products and Orders that has the following columns:

table Products: ProductID, ProductName
table Orders: OrderID, ProductID, OrderDate, Quantity, Price

The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.

I tried the following select statement:

select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'


Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :

ProductName / Quantity / Price
Product-A 5 1.89
Product-B 6 2.43
Product-C null null

Obviously, my sql statement won't work becaue the where clause will filter out Product-C.

Could anyone help me figure out how to modify my sql code to get the resultset I want?

Thanks in advance.

View 2 Replies View Related

Compressing Multiple Rows With Null Values To One Row With Out Null Values After A Pivot Transform

Jan 25, 2008

I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?

-- Ryan

View 7 Replies View Related

What Is Better? Return All Rows Or Nested TOP X?

Feb 24, 2006

I have a database that has 100,000+ records in a table. Am I better off returning all of the records from a search even if it is 50,000 records or is it better to do a SELECT COUNT(*) And nested SELECT TOP x statements to only return 1 page of results? What is the best practice for a situation like this?
SELECT * FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another.... AND...
OR
SELECT COUNT(*) FROM myTable LEFT OUTER JOIN ...        LEFT OUTER JOIN......WHERE something=@something AND another...        AND... ORDER BY @OrderAnd
SELECT TOP(@pagesize) FROM      (SELECT TOP(@pagesize * @pagenum) FROM myTable LEFT OUTER JOIN ...        LEFT OUTER JOIN......WHERE something=@something AND another...        AND... ORDER BY @Order)ORDER BY @revOrder
 

View 3 Replies View Related

Return A Row With Null Values?

Apr 19, 2014

I am trying to return all the names of employees and their managers

this query returns all the employees except for 1

SELECT E.FNAME,E.LNAME,M.FNAME,M.LNAME
FROM EMPLOYEE E,EMPLOYEE M
WHERE E.SUPERSSN=M.SSN

the one that isn't returned has a null SUPERSSN, but when I add in:

OR E.SUPERSSN IS NULL

it returns a row with the name of the employee whose SUPERSSN is null 8 times (where each time the M.FNAME,M.LNAME are other employee names)

How do I ammend the first query to return each employee and their respective manager once, the employee without a manager having null values for the manager name columns?

View 1 Replies View Related

How To Return No Rows If A Variable Is NULL

Apr 1, 2008

Hello,
I have a stored procedure that accepts a number of different input parameters that populate some variables in my stored procedure.
I want to have this stored procedure return nothing if some of these variables aren't filled out (they are populated by a search page the user fills out).
I'm not very familiar with writing stored procedures, so any help you can give me is appreciated.
Thanks!

View 2 Replies View Related

Check For Null Or Empty Values And Return 0

Mar 13, 2014

I am using the below query to calculate column values. But I need to return zero when a column values is empty or null.

select [Funding] [Fundings],
[Original] AS [Originals],
[Variance] = SUM([Previous_Year]-[Current_Year]),
[SumValue] = SUM([CurrentYear]/4),
[ActualValue] = SUM([Variance] * 0.75),
[FinanceYear],
[New Value] = SUM([Previous_Year]+[Current_Year])
from Finance
GROUP BY [Original], [FinanceYear]

View 1 Replies View Related

Return Null Values Using Coalesce And Nullif

Jan 9, 2008

Hello:

I'm creating a 'dynamic where clause' with 15 parameters. I was using coalesce with the example below and it's working fine until it encounters a null value. I was trying to use nullif with coalesce to no avail. Can someone show me based on my example below how I can incorporate nullif with coalesce. Thanks in advance for you help.




Code Block
WHERE cs.DirID = Coalesce(@DirNum, cs.DirID)

View 12 Replies View Related

Help With A Join To Return Unique Values

Apr 28, 2008

Hi All,

I need a bit of help with a join. I have 2 tables :

TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue

Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue

TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID


and what I want to retreive is :


For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the record for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID

Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID

thanks in advance

View 7 Replies View Related

How To Write A Query To Return Null For Non-exist Record In An Outer Join.

Jun 2, 2004

I have two tables:

1. tblProducts
columns: ProductID, ProductName

2. tblProductCodes
columns: ProductID, CustomerID, ProductCode

The 2nd table is for storing product codes for customers, in other words, one product can have different ProductCode for different customers. But some customers do not have ProductCode for a ProductID.

I want to create a query to return all the Products and its ProductCode (null is valid) for a specific customer.

I tried:

SELECT dbo.tblProductCodes.ProductCode, dbo.tblProductCodes.CustomerID,
dbo.tblProducts.ProductName,
dbo.tblProducts.ProductID
FROM dbo.tblProducts LEFT OUTER JOIN
dbo.tblProductCodes ON dbo.tblProducts.ProductID = dbo.tblProductCodes.ProductID
WHERE dbo.tblProductCodes.CustomerID = 2

But the query left out all products that does not have ProductCode value in tblProductCodes table for CustomerID = 2. I want all the ProductName returned from query and render null or empty string for ProductCode value if the code does not exist in tblProductCodes table for the customer.

Any help is highly appreciated.

View 4 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

Transact SQL :: Check 2 Columns And Return Rows As Long As One Column Is Not Null

Oct 19, 2015

how to do a check for 2 columns. As long as there is data for at least one of the columns I want to return rows.

Example Data

create table test
(
ID int,
set1 varchar(50),
set2 varchar(50),

[code]....

View 4 Replies View Related

Showing Null Values From ON Inner Join Sproc CLAUSE

Jul 19, 2006

Hi all
My query has some inner joins to some tables. And problem is when any ON clause get null as value, the correspondent record is not displayed.
SELECTTableA.A, TableB.AFROM TableAINNER JOIN TableB ON TableA.A = TableB.A
What I did try:
SELECTTableA.A, TableB.AFROM TableAINNER JOIN TableB ON TableA.A = TableB.A OR TableA.A IS NULL
(but It generates redundant values from TableB)
I need to show all values even that value from Tablea is null
Thank a lot for any help

View 1 Replies View Related

Group By After Outer Join - Getting Duplicate Or Null Values

Oct 18, 2013

I've got 2 tables of towns. I'm using outer join because i need all the town from both tables. However I'm sometimes getting duplicates.

My query

select a.town, b.town
from a
outer join b on a.town = b.town
group by a.town, b.town

How to stop getting null values?

portsmouth null
portsmouth portsmouth
southampton southampton
null southampton
TownA null
null TownB

I'm looking for distinct values like this:

portsmouth portsmouth
southampton southampton
TownA null
null TownB
etc...

View 2 Replies View Related

Left Outer Join Not Returning All/null Rows

Dec 31, 2007

Need some join help...

Table 1 - Modules:
ID | Name
1 | A
2 | B
3 | C

Table 2 - CompanyModules
ModuleID | CompanyID
1 | 1
2 | 1
3 | 1
1 | 2


I'd like to return the following result set:
CompanyModules.CompanyID | Modules.Name | Present
1 | A | True
1 | B | True
1 | C | True
2 | A | True
2 | B | False
2 | C | False

What would be the query for this? Thanks.


Edit: This is the query I have tried:


select CompanyModules.CompanyID, Modules.Name, count(Modules.ID) as Present from

CompanyModules RIGHT outer Join Modules on CompanyModules.ModuleID = Modules.ID

group By CompanyModules.CompanyID, Modules.Name

Order by CompanyID


However, it only returns a partial result set:

CompanyModules.CompanyID | Modules.Name | Present
1 | A | 1
1 | B | 1
1 | C | 1
2 | A | 1

View 1 Replies View Related

Transact SQL :: Preserve And Return NULL For Non Matching Values From A Table Valued Function

Jun 29, 2015

I have tables and a function as representated by the code below. The names  for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.

When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.

SELECT  A.CID, 
A.ANI,
A.PID,
B.RD
FROM T1 AS A CROSS APPLY GIVERD(A.PID) B

CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)

[Code] ....

View 4 Replies View Related

Having Rows With Some Null Values Returned Conditionally

Aug 8, 2012

This should be a simple solution, but it has been a long time since I've done any query writing (mostly in Oracle) and I am stumped, so here goes:

We are in the process of converting Access database to MSSQL with web form front ends.

I have a table, all columns are nullable, and want users to be able to query from a form, which has a field for each column and defaults to a % wild card for the entered value.

I want the users to be able to put any string in any field, and have it return each row that matches that, including rows with null values in the other columns, but not the column with the entered criteria.

Here is a sample of the data:

Code:
SQL> select * from test;

COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is not
this is test too
is test too
is too
is too

7 rows selected.

Now, if I have this SQL run, it will return only rows that have no nulls in any columns:

Code:
select
col1,
col2,
col3,
col4from test
where
col1 like'th%'
and col2 like '%'
and col3 like '%'
and col4 like '%';

COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is test too

Now, if I use an OR clause for each column, this mostly works, but the trouble is it will also return rows with null values for the field that has criteria entered in it:

Code:
select
col1,
col2,
col3,
col4from test
where
(col1 like'th%' OR col1 is null)
and (col2 like '%' OR col2 is null)
and (col3 like '%' OR col3 is null)
and (col4 like '%' OR col4 is null);
COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is not
this is test too
is test too
is too
is too

The idea is to only select the first 4 rows in the above example.

I was playing with ISNULL in the select clause, but all it does is substitute a string for a null, and I think CASE will do the same thing.

Is there a way I can write this query so it will return rows with NULL values in any column, except the one(column) that has user entered criteria in it?

View 9 Replies View Related

Won't Select Rows With Null Field Values

Aug 29, 2006

I cannot in the life of me understand what goes wrong here... In a webapplication (C#.NET) I traced an inability to retrieve existing records to SQL Server, where I cannot do the same either. The problem is that in the parameterized query, some fields can be null, and thus when the corresponding fields in the database record are null also, they should be selected. But this won't happen for some reason.

I wrote a test SQL statement that gives me the same bogus:

DECLARE @institution int, @collection int, @serialnr int, @subnr nvarchar(50)SET @institution = 1 SET @collection = 1SET @serialnr = 240 SET @subnr = NULLSELECT ID, Institution, Collection, SerialNumber, SubNumber, AccessionYear, Sagsnummer, DanekraeNr, TaxonIdentified, Stratigraphy, TypeStatus, PlacementRoom, PlacementCabinet, PlacementDrawer, UnitNotesFROM SpecimensWHERE (Institution = @institution) AND (Collection = @collection) AND (SerialNumber = @serialnr) AND (SubNumber = @subnr)
Now there is at least one row with corresponding fields values (1, 1, 240, null), but it won't be selected! What is wrong!?

View 4 Replies View Related

Return Tables With 0 Rows

Nov 20, 2006

Hello All
Please can anyone advice me how I can fetch list of all tables which have no records in it in sql server

Thanks

View 9 Replies View Related

Compare Rows In 2 Tables, Return Them If Different

Jan 9, 2008

Rows in table A that have a business status of 'open' are written to table B (on a different db) on a daily basis.

I have managed to extract the relevant rows in table A and table B.

What I now need to do is compare table A, row N with table B row N and return them both if the values differ.


What is the best way of doing this

e.g.

Table A

customerId amount
1 200
2 106 *return this row*
3 412


Table B (filtered by MAX(audit date))

customerID amount
1 200
2 100 *return this row*
3 412



?

View 6 Replies View Related

Join Two Tables And Only Return The Latest Data For The Child Table

Sep 24, 2007

I have two table, tblCharge and tblSentence, for each charge, there are one or more sentences, if I join the two tables together using ChargeID such as:
select * from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID
, all the sentences for each charge are returned. There is a field called DateCreated in tblSentence, I only want the latest sentence for each charge returned, how can I do this?
I tried to create a function to get the latest sentence for a chargeID like the following:
select * from tblCharge c join tblSentence s on s.SentenceID=LatestSentenceID(c.ChargeID) but it runs very slow, any idea to improve it?
thanks,

View 4 Replies View Related

Linked Server Doesn't Return All Rows For Some Tables

Jun 4, 2007

Hello,



I created a linked server in sql server 2005 which links to a AS400 DB. I use ODBC driver.

For some tables, it return all data but for another tables, it only return part of the rows.

How it may happen?



Thanks

View 1 Replies View Related

Joined Tables - Count And Null Values

Jan 11, 2014

I joined different tables and got a result like this:

result | process | goal | date |
------- ---------- ------ -----------
ok | process4 | 1 | 12.10.2013
bad | process1 | 2 | 13.10.2013
ok | process1 | 4 | 12.12.2013
good | process4 | 1 | 03.01.2014
ok | process1 | 3 | 10.04.2013
bad | process3 | 6 | 09.01.2014
bad | process4 | 3 | 30.12.2013
best |NULL| NULL

Now I want to count the results by counting the processes and group them by the result.

But it should be count the latest result per process only, e.g. for goal "1" just "good" at 03.01.2014. I solved that with a subquery (date=SELECT MAX(...)..).

But now the result "best" disappears, because that column has no date.

Secondly I want to count results for a specific process, e.g. for process4. Every goal has max. one process, with different dates. But one process could have more than one goal.

I want to have this result for process4:

count | result
------ -------
1 | good
1 | bad
0 | ok
0 | best

But I got only:

count | result
------ -------
1 | good
1 | bad

I have tried a lot, but nothing works.

The whole result (best, good, ok, bad) are stored in an other table and I joined it.

View 4 Replies View Related

Problem With Isnull. Need To Substitute Null If A Var Is Null And Compare It To Null And Return True

Sep 20, 2006

Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you

set ansi_nulls off


go

declare

@inFileName VARCHAR (100),

@inFileSize INT,

@Id int,

@inlanguageid INT,

@inFileVersion VARCHAR (100),

@ExeState int

set @inFileName = 'A0006337.EXE'

set @inFileSize = 28796

set @Id= 1

set @inlanguageid =null

set @inFileVersion =NULL

set @ExeState =0

select Dr.StateID from table1 dR

where

DR.[FileName] = @inFileName

AND DR.FileSize =@inFileSize

AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)

AND DR.languageid = isnull(@inlanguageid,null)

AND DR.[ID]= @ID

)

go

set ansi_nulls on

View 3 Replies View Related

Join Two Tables And Update Values In One

Feb 21, 2012

I have two tables A and b

A
projectID Setid value
301 1 abc
301 2 xyz
302 4 def
..... .... ..

B
SettingsId Setvalue
1 ter
2 yet
4 44
... ....

I want to update all the Setid of table A with that of values from table b. How can i do this?

View 7 Replies View Related

Join Tables On Sorted Values

Nov 19, 2013

Is there a way to join tables that have multiple matches to each other (2 records in one table and 2 in another) so that you get 2 records returned instead of 4 with only 1 JOIN ON qualifier?

In our warehouse DB, there is a master location table, an inventory location table, and physical table for counting all product in the warehouse. The master location table has one record per location, but there could be multiple items in that location so my outer join from the master location to the inventory table returns something like:

select M.MASTER_LOC, C.AS ORIG_ITEM, C.ORIG_LOT, C.ORIG_QTY
from
M_LOC M LEFT OUTER JOIN
C_INVT C ON M.MASTER_LOC = C.INVT_LOC
order by M.LOC_CODE

LOCATION ITEM LOT QTY
01-01-A 100 abc 25
01-02-A NULL NULL NULL
01-03-A 200 def 50
01-03-A 200 ghi 50

My problem is adding the third counted inventory table because it could look like anything depending on what we find in the racks:

LOCATION ITEM LOT QTY
01-01-A 100 abc 25 (exact match)
01-02-A 150 cba 75 (Item found)
01-03-A 200 ghi 50 (LOT swapped)
01-03-A 300 def 50 (Item Changed)

My join is returning 4 rows for location 01-03-A which I understand, but I'm wondering if I can sort within the join or make some temp tables so that instead of:

select M.MASTER_LOC, C.AS ORIG_ITEM, C.ORIG_LOT, C.ORIG_QTY, E.AS CNTD_ITEM, E.CNTD_LOT, E.CNTD_QTY
from
M_LOC M LEFT OUTER JOIN
C_INVT C ON M.MASTER_LOC = C.INVT_LOC LEFT OUTER JOIN
E_PHYS_INVT E ON M.MASTER_LOC = E.LOC_CODE
order by M.LOC_CODE

LOC1 ITEM LOT QTY LOC2 ITEM LOT QTY
01-03-A 200 def 50 01-03-A 200 ghi 50
01-03-A 200 def 5001-03-A 300 def 50
01-03-A 200 ghi 5001-03-A 200 ghi 50
01-03-A 200 ghi 5001-03-A 300 def 50

I'd like to just end up with 2 lines sorted by location, item, lot, qty so I can see that there is a problem with that location.

LOC1 ITEM LOT QTY LOC2 ITEM LOT QTY
01-03-A 200 def 5001-03-A 200 ghi 50
01-03-A 200 ghi 5001-03-A 300 def 50

View 2 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

SQL Server 2008 :: Join And SUM Values From 2 Tables

Jan 29, 2015

I am new in SQL and i need do a query where I need sum values from 2 tables, when i do it the Sum values are not correct. this is my query

SELECT D.Line AS Line, D.ProductionLine AS ProductionLine, D.Shift AS Shift, SUM(CAST(D.DownTime AS INT)) AS DownTime,
R.Category, SUM(Cast(R.Downtime AS INT)) AS AssignedDowntime,
CONVERT(VARCHAR(10), D.DatePacked,101) AS DatePacked
FROM Production.DownTimeReason R
left JOIN Production.DownTimeHistory D

[Code] .....

View 3 Replies View Related

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

I have a stored procedure that selects the unique Name of an item from one table. 

SELECT DISTINCT ChainName from Chains

For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.

SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName

Each row of the result set returned by the stored procedure would contain:

ChainName, Array of StoreNames (or comma separated strings or whatever)

How can I code a stored procedure to do this?

View 17 Replies View Related

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 View Related

SQL Search :: DELETE Rows From All Tables In Database Where Column Name Is Geo And Value Is NULL

Nov 16, 2015

I need to look at all tables in a database that has a column name of GEO

Then look for all values in each table where the GEO value is NULL and delete each of the records found...

View 6 Replies View Related

SQL 2012 :: Join Tables And Only Pull Back Certain Values?

Oct 22, 2014

I am having problems joining these two tables and returning the correct values. The issue is that i have a work order table and a revenue table. I only want to return the sum of the revenue when the revenue comes after the work order date. That is simple enough, but it gets tricky when there are multiple work orders for the same ID. for those instances, we only want the sum of the revenue if it shows up post the work order date and if it is before any other work order date. So ID 312187014 should only have the 9-5 revenue from below, not the 7/7 or 8/6 revenue because the 8/7 work order date is after those revenue dates and thus will not have any revenue tied to it because there is a 9/3 work order that ties to the 9/5 revenue. Additionally the 412100368 ID has a 7/7 work order that ties to the 7/26 revenue, and the 8/7 work order will tie to the 8/23 and 9/20 revenue

--===== Create the test table with

CREATE TABLE #workorder
(
Id varchar(20),
wo varchar(10),
wodate datetime,
amount float
)
GO
CREATE TABLE #revenue

[code].....

View 2 Replies View Related







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