How To Search Multiple Table Which Table Name Is Store In Another Table And Join The Result Together?

Dec 1, 2006

I have one control table to store all related table name

 Table ID                   TableName

     1                           TableA

     2                           TableB

 

In Table A:

RecordID                Value

     1                         1

     2                         2

     3                         3

 

In Table B:

RecordID             Value

    1                         1

    2                         2

    3                         3

 How can I get the result by select the Table list first and then combine the data in table A and table B?

 

Thank you!

View 1 Replies


ADVERTISEMENT

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 Table Key To Multiple Table Names

Nov 5, 2004

Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:

I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.

That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.

Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.

Thx

View 12 Replies View Related

Store The Result Of Xp_cmdshell In A Table

Sep 3, 2004

hai..

is it possible to store the result the following statement in a table

execute master..xp_cmdshell 'dir'

when i execute this in QA i get the result. but i want to store it in a table

thnks in advance

with regards
Sudar

View 6 Replies View Related

How To Store Query And Result On The Same Table

Jun 13, 2008

One column in my table stores SQL queries(QueryCoulmn). Another coulmn supposed to store the result of those queries(ResultColumn). Can I run an update query or how can I do that? I could not figure out the syntax.

update tablename
set ResultColumn=exec(QueryCoulmn)

thanks

View 6 Replies View Related

SQL 2012 :: Split Data From Two Columns In One Table Into Multiple Columns Of Result Table

Jul 22, 2015

So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:

**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3

**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3

In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID

I am trying to create a table such as shown below:

Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich

This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.

For e.g

Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One

[Code] .....

The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.

View 4 Replies View Related

Store Result[index] From Table In Local Var

Apr 30, 2008

Hi,

Can I take the [index] result from a table and store it in a local var?
For example a table

declare @keyWords(
word varchar(40),
wordLen smallint
)

word len
---- ---
test 4
t 1
e 1

declare @index smallInt = 2
SET @keyWord = SELECT TOP @INDEX word FROM @KeyWords

This obviously is not correct for several reasons (TOP returns multiple results, TOP doesn't work with local var etc)

View 2 Replies View Related

Transact SQL :: How To Store Result Of Exec Command Into Temp Table

Apr 7, 2013

I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:

Insert into #temp
Exec(@sqlcommand)

For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.

View 17 Replies View Related

Join The Result Of Xp_logininfo With A Table

Jul 23, 2005

Hello group!i have a table "group_code" wich relates the names ofnt-(domain)-groups to codes. now i want use the stored procedurexp_logininfo (asking for the group-membership of the current user) tojoin the result to "group_code". then i must use the new result (thecode) to join against other tables.i know now, that i cant join results of SPs against tables. may be thata UDF with a table result is the correct approach. but i have no ideahow to wrap the xp_logininfo in a UDF.a other way can be to do in a UDF the same thing like the xp_logininfo.then this UDF should deliver at least the nt-(domain)-groups in wichthe current-user is a member.Is there anybody who can give me the code for that?many thanx in advance.Karl

View 1 Replies View Related

Incorrect Order Result Set When Join Table

Mar 24, 2002

Hi all,
I faced a problem, I have two tables - part and partmaster
part : part_no, part_qty (no key)
partmaster : part_no, part_description (primary key : part_no )

I want to select table part.* and partmaster.part_description.

(run on mssql 2k)
select a.*, b.part_description
from part a, partmaster b where a.part_no *= b.part_no

I want to and expect to have the result order like table "part". However, after the join, the order is different. I try to run it on mssql 7.0, the order is ok.

Then I modify and run the statement select a.* from part a, partmaster b where a.part_no *= b.part_no on 2k again. The result order is ok.

can anyone tell me the reason?

Now I try to fix this problem is adding a sequence field "part_seq" into table "part" and run the statement by adding a order by part_seq.
It does work!

Regards,
Simon

View 1 Replies View Related

Join Category Table So That Categories Are In The Result On Only

Feb 22, 2007

SELECT DISTINCT Image.ImageID, Image.JobID, Image.Filename, Tag.Name,
Tag.SortOrder, TagCat.name

FROM Image, JobTag, Tag, Job, Tagcat

WHERE Tag.TagID = JobTag.TagID
AND Image.JobID = Job.JobID
AND Job.JobID = JobTag.JobID
AND TagCat.TagCatID = Tag.TagCatID

ORDER BY Image.ImageID, Tag.SortOrder


I'd like to replace the query above with a some kind of a join, but I'm not sure how to do it. The reason I want to do this is to only get the category (TagCat.name) one time for each set of tags that go under that category for that particular job. Any thoughts on how I could do this? Would I need to create more than one query or do you think this can be one with just one query?

View 1 Replies View Related

Duplicate Result Rows From 2 Table Join

May 7, 2012

I am using SLQ Server 2008 R2. The database was designed by another company.

I have two tables: Client and Client_Location. In the Client table the pk is Client_ID. There is also a unique key: sys_Client_ID. Both the Client_ID and the sys_Client_ID fields exist as a foreign keys in the Client_Location table. However, the fields are not noted as unique in the Client_Location table. There are two fields in the Client_Location table that determine when the address was effective. They are from_dt and end_dt.

Multiple records have been loaded into the Client_Location table to track old as well as current addresses of clients.

I'm trying to run a report that will pull clients with a plan_id constraint from the Client table and join the Client_Location table to retrieve the current address of these clients.

My SQL is:

select distinct (a.client_id), a.cli_last AS Last_Name,
a.cli_first AS First_Name, a.cli_middle AS Mid_Init,
b.city AS City, b.county AS County, b.state AS State
from ECBH.dbo.tbl_Client a inner join ECBH.dbo.tbl_Client_Location b
on a.client_id = b.client_id
inner join ECBH.dbo.tbl_client_insurance c
on a.client_id = c.client_id
inner join ECBH_TEST.dbo.tbl_GEF_County d
on b.county = d.COUNTY_NAME
where c.plan_id = 4
order by a.cli_last, a.cli_first

Because multiple records exist in the Client_Location table, the result set has duplicates. How can I pull only the results where the from_dt is most recent?

View 5 Replies View Related

Trying To Join Within A Table - Get Result Sorted By Primary ID

Aug 27, 2014

So if an item is a bottle, it will have a unique BottleKey and Null for CaseKey and if an item is a Case, it will have a unique CaseKey and Null for BottleKey. However, the PrimaryIDs are the same for both the bottle and case. I get the results to look like this:

Bottlekey CaseKey PrimaryID
4754 NULL ABC-234
NULL 5465 ABC-234
.... .... .......

Is there a way to get the result sorted by Primary ID?

So that the rows are halved from what we have in the table above and the results look like

PrimaryID CaseKey BottleKey
ABC-234 5465 4754

View 1 Replies View Related

Subject: How To Join A Table With Other (result) Tables ? Complex !

Aug 25, 2005

Here is the situation

Table 1 : tbl_documents

docIDdocName
1aaa
2bbb
3ccc

Table 2 : tbl_Rating

ratIDratingdocID
131
251
321
432

The queary I need is to display the result in this form. must be like this

docIDdocName Avaragerating
1aaa3
2bbb3
3ccc0

NOTE : For getting the average I used this queary “SELECT SUM(rating) As
RatingSum, COUNT(*) As RatingCount FROM tbl_Rating WHERE tbl_rating.docID =
tbl_documents.docID”

PLs help me ?

Thx

View 3 Replies View Related

Transact SQL :: Create Email Report Which Gives Result Of Multiple Results From Multiple Databases In Table Format

Jul 24, 2015

I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:

EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary', 
@profile_name  =
'SQL SMTP',
   
[code]....

View 3 Replies View Related

Multiple Column Result From One Table

Jul 20, 2005

Hi,I have a table like this :TimeIDApplicationLoginState1App1login101App2login211App3login311App1login401App1login511App4login102App1login102App2login202App3login312App1login402App1login512App4login103App1login103App2login213App3login313App1login403App1login513App4login10And I want a result like thisTimeID (state=0) (state=1)1 3 32 4 23 3 3I use this code :set nocount ondeclare @timeid1 intdeclare @timeid2 intdeclare @timeid intdeclare @sessionstate intdeclare @count1 intdeclare @count2 intdeclare @count intdeclare @aux intdeclare txt_cursor cursor forselect timeid, sessionstate , count(login) from metromasterwhere sessionstate = 0group by timeid, sessionstateUNIONselect timeid, sessionstate , count(login) from metromasterwhere sessionstate = 1group by timeid, sessionstateorder by timeidopen txt_cursorselect @aux = 0fetch next from txt_cursor into @timeid, @sessionstate, @countwhile (@@fetch_status = 0)beginif @aux = 0beginselect @timeid1 = @timeidselect @count1 = @countselect @aux =1endelsebeginselect @timeid2 = @timeidselect @count2 = @countselect @aux = 2endif @aux = 2begin--select @timeid1, @count1, @sessionstate, @count2, @timeid2select @timeid1, @count1, @count2select @aux = 0endfetch next from txt_cursor into@timeid, @sessionstate, @countendclose txt_cursordeallocate txt_cursorset nocount offBut it create a lot of blank row and field header.Does anyone know an other methode ??Thanks.

View 1 Replies View Related

Search For Items Across Multiple Table...

Oct 24, 2007

I have been working on a database and I have imported a number of lists into multiple tables.

I am having a lot of trouble writing the SQL code to be able to query across multiple tables. For instance, I want to know if john doe is on all five lists or if he is only on 3 lists (search by student name).

Can someone help me with this?

Luin.

View 2 Replies View Related

Multiple Join On Same Table

Jan 1, 2014

I am struggling with joining on the same table and cannot seem to get my head around the solution.

I have three tables as follows:

Patients Samples Results
UniqueID UniqueID
Surname LabNumber LabNumber
Forename (etc.) Sampled Code
Result

The code is the name of the test and the result is just that. Not all patients will have the same set of results. What I'd now like to do is pull out a CSV file of all results from 2013 including where the result is null. The format I am looking for is along the following lines:

Sex, Age, AKIN2, AKIN7, RIFLE,

The AKIN2, AKIN7 and RIFLE are the codes in the result table. I have tried OUTER JOINS but it seems to only pull out those records that exist (example below). I need to specify the result codes I am interested in otherwise the output could be enormous but this then does not pull out null values (a NULL value is an important as an actual value).

SELECT
Samples.Patient_ID AS ID,
Samples.LabNo As LabNo,
Patients.Sex As Sex,
DATEDIFF(year, Patients.DoB, Samples.Sampled) AS Age,
Samples.Source as Source,

[Code] ....

View 2 Replies View Related

Multiple Table Join

Oct 10, 2006

Ok, this is what I am trying to do:,
1) I am trying to get the number of employees that has completed all their
online training within 10 days of hire
2) All the employees that are has no exception(no pre-service training) and
has completed their checklist within 10 days
3) all the exception(pre-service) employees that has completed their
training within 70 days.

I have these tables, hipaa2006, hipaa101, hipaa201_inputs,
domesticviolence, securityawareness, securityawareness2006,
civilrights_input, and people_first_data.
People_first_data contains all the employees we have in our database, it
has empl_pfid col, but does not have last_modified col which all the other
tables has.

I have this queries:

1) select distinct(count(empl_pfid))
from people_first_data
left outer join tbl_domesticviolence on people_first_data.empl_pfid =
tbl_domesticviolence.employee_pfid
left outer join tbl_hipaa101 on
people_first_data.empl_pfid=tbl_hipaa101.employee_pfid
left outer join tbl_hipaa201_input on people_first_data.empl_pfid=
tbl_hipaa201_input.employee_pfid
left outer join tbl_hipaa2006 on
people_first_data.empl_pfid=tbl_hipaa2006.employee_pfid
left outer join tbl_securityawareness on
people_first_data.empl_pfid=tbl_securityawareness.employee_pfid
left outer join tbl_securityawareness2006 on people_first_data.empl_pfid=
tbl_securityawareness2006.employee_pfid
where people_first_data.empl_pfid = '639846'
and tbl_domesticviolence.last_modified is not null
and tbl_hipaa101.last_modified is not null
and tbl_hipaa201_input.last_modified is not null
and tbl_hipaa2006.last_modified is not null
and tbl_securityawareness.last_modified is not null
and tbl_securityawareness2006.last_modified is not null

2) ( I tried union, but i only wanted one number)
select count(last_modified) as date1 from tbl_hipaa2006
union
select count(last_modified) as date2 from tbl_hipaa101

Your help will be highly appreciated.

View 1 Replies View Related

Does It Store All The Results To Tempdb Database When I Query Against A Large Table Which Joins Another Table?

Jun 25, 2007

Hi, all experts here,



I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.



With best regards,



Yours sincerely,



View 11 Replies View Related

Using A Match Table To Store Multiple Columns For Parent Data

Mar 1, 2008

Sorry for the confusing subject. Here's what im doing:I have a table of products. Products have N categories andsubcategories. Right now its 4. But there could be more down theline so it needs to be extensible.So ive created a product table. Then a category table that has manycategories of products, of which a product can belong to N number ofthese categories. Finally a ProductCategory "match" table.This is pretty straigth forward. But im getting confused as to how towrite views/sprocs to pull out rows of products that list all theproducts categories as columns in a single query view.For example:lets say productId 1 is Cap'n Crunch cereal. It is in 3 categories:Cereal, Food for Kids, Crunchy food, and Boxed.So we have:Product----------------1 Capn CrunchCategories-----------------1 Cereal2 Food for Kids3 Crunchy food4 BoxedProductCategories------------------1 11 21 31 4How do I go about writing a query that returns a single result set fora view or data set (for use in a GridView control) where I would havethe following result:Product results---------------------------------ProductId ProductName Category 1 Category 2Category 3 Category N ...------------------------------------------------------------------------1 Capn Crunch Cereal Food for Kids Crunchy foodBoxedAm I just thinking about this all wrong? Sure seems like it.Cheers,Will

View 1 Replies View Related

Integration Services :: How To Search A Table In Multiple Packages

Dec 3, 2015

I have 5-6 table and wanna to make sure if I'm using any of them in my Packages and I do have 100 Packages.shortest way to search these tables in Packages.

View 2 Replies View Related

Using Result Of A Select Statement From One Table To Add Data To A Different Table.

Mar 17, 2008

I have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table? 

View 3 Replies View Related

Table-Valued Function Result Vs. Calculation Table

Jun 6, 2006

 

I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.

Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:

 DataUserID, StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...

Or using a table-valued function to return a temp table as the result.

I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call. 

View 3 Replies View Related

SQL Server 2012 :: Capture Changes In Table And Store It In Another Table

Dec 25, 2014

I need to capture changes in my table and store it in another table. Here is how my table would look like.

CREATE TABLE [dbo].[ABC](
[Hno] [nchar](10) NULL,
[Status] [smallint] NULL,
[Date] [datetime] NULL
) ON [PRIMARY]

[Code] ....

In return I need to check column Status for whenever the value has changed and need to store that in my table. If there are two records for which value in column Status is same, I need to pick only one of the records and that being the earliest of them and therefore the Date field is mentioned in my table . My output should be something like below.

100 02014-12-20 00:55:44.667
100 12014-12-22 00:55:44.723
100 02014-12-24 00:55:44.723
100 12014-12-26 00:55:44.723

View 4 Replies View Related

SQL Server 2014 :: Large Table With Multiple Search Columns

Jun 23, 2015

I've a database with a table that has 16 columns that are searchable. There can be a numerous combination of those columns used for searching...

In this case the best solution is to create an index on each column individually or at least the most used?

View 7 Replies View Related

Using Outer Join In Multiple Table Query

Dec 16, 2004

What is the best way to use a left join in a SQL statement with multiple tables (more than 2)? I have a query that uses 7 tables, where most of the joins are inner joins, except for one, which needs to be a left join. The current SQL statement looks something like this:

SELECT [table1].[field1], [table2].[field1], [table3].[field1], [table4].[field1], [table5].[field1], [table6].[field1], [table7].[field1]

FROM [table1],[table2],[table3],[table4],[table5],[table6],[table7]
WHERE
[table4].[field2]=[table1.field2]{this is an inner join}
[table4].[field2]=[table2.field2]{this is an inner join}
[table4].[field2]=[table3.field2]{this is an inner join}
[table4].[field2]=[table5.field2]{this is an inner join}
[table5].[field3]=[table6.field2]{this is an inner join}
[table5].[field4]=[table7.field2]{this is needs to be a left join}

As it stands now, the last line in the WHERE clause is an INNER JOIN and limits the number of rows in my result. I need to select rows from [table7].[field2] whether or not a matching record exists in [table5].[field4]. The other INNER JOINS in the SQL statement must have matching records. Please advise.

View 2 Replies View Related

Integration Services :: Store Multiple Config In Single Table Using SSIS Configurations?

Oct 20, 2015

I want to store multiple config files into single table I am unable to do it.

View 8 Replies View Related

SQL Server 2012 :: Multiple Level Join On Same Table?

Oct 10, 2015

i have below queries each select is fetching records at one level. Is there a way i can write single query to get to nth level (recursion) instead joining same table 10 times (i don't know in some cases there is may be next level) I stopped at 10th level now. In below example i gave only two levels.

SELECT Distinct
a.Col1 AS EmpID,
a.Col1 AS EmpID,
a.Col2 AS Emp_guid,
a.Col2 AS Emp_guid,
case

[code].....

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

SQL Server Max Record, Multiple Table Join Problems

Jul 20, 2005

Thanks in advance for you help.SQL Server 2000I have a complex join, but a simple example will do the trick so...table1 (placement records, possibly many per case,highest ID beingmost recent)----------placementID(pk) * CaseID(fk) * OrganizationID(fk) * Name * Numbertable2 (Organizations Table, Many Placements can be at 1 organization)----------OrganizationID(pk) * OrgName * OrgTypetable 3(Case Table, each Case can have many placements)----------CaseID(pk) * StaffName * CreationDatenow my query...SELECT t1.placementID,t1.caseID,t2.OrgNameFROM table1 as t1INNER JOIN table2 as t2 on t2.OrganizationID = t1.OrganizationIDWHERE exists (select distinct max(placementID),CaseIDfrom t1 group by caseID)GROUP BY t2.OrgName,t1.PlacementID,t1.CaseIDmy results-------------placementID CaseID OrgName1 1 oneOrg2 1 two org3 1 three org4 2 another org5 3 yet another orgmy desired results------------------PlacementID CaseID OrgName3 1 three org4 2 another org5 3 yet another orgas you can see i get all records, but what i want is to see only thelast placementID for each case so i dont want duplicate caseID but Ido need the orgName, and yes the query works correctly without the orgname but as soon as i add orgName to the select statement I getduplicate CaseID's, How do i eliminate duplicate CaseID's and get onlythe MAX(placementID) for each Case and the OrgNameplease advise, getting desperate.thanks again so much for the help

View 1 Replies View Related

One Table, Two Condition, Display Result As One Table

Nov 29, 2004

I got one table with 3 columns = Column1, Column2, Column3

Sample Table

Column1 | Column2 | Column3
------------------------------------
A | 12 | 0
A | 13 | 2
B | 12 | 5
C | 5 | 0

Select Column1, Column2, Column3 as New1
Where Column1 = A AND Column2 = 12 AND Column3 = 0

Select Column1, Column2, Column3 as New2
Where Column1 = A AND Column2 = 12 AND Column3 >0

The only difference is one condition Column3 = 0 and another one Column3 > 0. This two condition is not an "AND" condition... but just two separate information need to be display in one table.
So how do i display the result in one table where the new Output will be in this manner

Column1 | Column2 | New1 | New2|

Thanks

View 3 Replies View Related

SQL Server 2012 :: Join To Find All Records From One Table That Do Not Exist In Other Table

Apr 29, 2014

I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.

What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:

'allstores' 'Stores'
storeid doc article storeid doc article
ALL 0010 001 101 0010 001
ALL 0010 002 101 0010 002
ALL 0011 001 102 0011 002
ALL 0011 002

So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".

View 7 Replies View Related







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