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)Â
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.
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)
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:
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:
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.
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.
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
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!
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?
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?
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:
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”
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]....
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).
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,
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
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.
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
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.
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?
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:
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.
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:
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.
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
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
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
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
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".
