SEARCH YOUR SOLUTION HERE  

aggregates from two unrelated tables

Hi

I have similar problem and i explained in detail here http://www.balainfo.com/forums/viewtopic.phpt=39

Pls help me to fix this sql

Regds
Bala

Posted On: Friday 26th of October 2012 12:05:26 AM Total Views:  411
View Complete with Replies




Related Messages:

Combining info from two tables?   (442 Views)
, I'm having some problems trying to access two tables in a SQL database at the same time and making some results out of them. Let me explain further: the first table has some information in that I'm going to be doing a select query on and reading out, but one of the columns in this table is a set of codes, the second table contains the codes in one column and their meanings in the other. So I want to bring back the information from the first table and then select the information for the codes shown from the second table and print their meanings alongside the information from the first table. Could anyone help me out in figuring out how my SQL in the ASP page for this would be written Sorry if this is a little confusing but im having a hard time visualising how to do this.
two select statements on one table   (338 Views)
Im trying to perform the following two select statements on the one table. I have been trying innerjoins etc but keep getting errors. The basis of what im trying to do is this. SELECT column1 FROM table1 WHERE column2 = (select column2 from table1 where column3 = 14) Any ideas greatly appreciated.
How to ensure the heart beat network is working fine ?   (188 Views)
How to ensure the heart beat network is working fine isAlive and keepAlive are working fine Is there any command to test the we dont have any problems before installing a cluster Thank you.
Can't Connect to SQL Server Express 2008 Network Database   (152 Views)
First off, I don't know much about networking which is probably why I am having problems. I do know that I have two computers that connect to the internet via a router. The IP of the computer running the SQL Server is 192.168.0.71 and the computer name is SEAN. The other computer is the one that is trying to connect to the SQL Server; it is named MATT and it's ip is 192.168.0.114. From MATT I can ping 192.168.0.71 and also I can ping this machine by typing 'ping sean'. When I do this it starts off by saying 'Pinging sean.gateway.2wire.net' etc. From SEAN I can ping 192.68.0.71 and also I can ping again using 'ping matt'. Also here, matt is listed as 'matt.gateway.2wire.net'. I am hoping this background info is useful :-\. So the problem is, from my .NET application running on SEAN I can connect to the SQL Server running on SEAN (SEAN\SQLEXPRESS), but when I run the same .NET application on MATT, I have been unable to do the same. These are the connection strings I have tried: Data Source=\\SEAN\SQLEXPRESS;Initial Catalog=SmartBidDatabase;User ID=sa;Password=SmartBid Network Library=DBMSSOCN;Data Source=192.168.0.71,1433;Initial Catalog=SmartBidDatabase;User ID=sa;Password=SmartBid Server=Sean\SQLEXPRESS;Network Library=DBMSSOCN;Data Source=192.168.0.71,1433;Initial Catalog=SmartBidDatabase;User ID=sa;Password=SmartBid I'm not sure if I'm doing something completely wrong with the connection string of if the network setup is getting in the way. With the last connection string I made sure to go into My Computer -> Manage -> Services and Applications -> SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for SQLEXPRESS -> TCP/IP , and enable it with a port # 1433. Oh, and both computers are running Vista with no firewalls on (not even Windows Firewall).
Retrieve data based on two different date fields   (134 Views)
Hi , I tried the following sql query to retrieve data from two different date fields in my vb6 application. but the 0 data is retrieved. please help me. The query is: rs.open "select product1 from table1 where fromdate=#" & dt1 & "# and todate=#"& dt2 & "#", con,1,3
Transfer data between two tables in a database   (193 Views)
Hi all, I am using MsSQL 5.0 server database. I need to transfer the data from one table to another within a SQL database based on the particular time interval. Is it possible to transfer data among the tables a database automatically. Please help me.
Connect SQL Server on Network   (128 Views)
I have two copies of SQL Server installed on seperate machines with seperate named instances on each machine, both these work fine individually but I want to be able to connect from one to the other and vice versa. They are both Windows XP machines setup on my home network and there is no domain setup or anything like that as I don't know how to do that. Has anyone had any experience of this kind of thing If so please help! Cheers, Vidal
[Help] Network SQL text   (142 Views)
I need help i have 2 tables, 1. profile 2. friends now i need to show my friends network until level 3 sample: i have 3 friends and all of my friend have 3 another which is maybe the same person (me) (Steven) (Olive) (Andrew) (Joy) (Olive) just like that i hope there someone can help me Best regards William
SQL Server Backups (Network)   (138 Views)
Our e-business group will be running SQL Server as a backend and C#, ASP.NET, VB.NET application on the front end. Besides my enternal backups of user data on the database. What would you suggest for the Network team to backup and how offen. This is for productions. I usally get a full drive dump on the Dev network daily appended,and weekly archived. Any suggestion for production.
Merging two records from the same table in SELECT   (139 Views)
I have a function that returns author name to a given title. It is based on the table [title_has_author]: title author 1 1 1 2 2 3 The function only returns and cleans the name of the first author. I'd like it to do the same with the second (or n authors), but I'm nonplussed.. Can anybody help Here's the function: ALTER FUNCTION [dbo].[fn_getAuthors](@idTitle int) RETURNS VARCHAR(MAX) AS BEGIN declare @p VARCHAR(max) declare @r VARCHAR(max) set @p='' select @p = @p + a.first_name + ' ' + a.last_name + ', ' from author a, title_has_author tha where a.idauthor = tha.author and tha.title = @idTitle select @r = case when right(rtrim(@p),1) = ',' then substring(rtrim(@p),1,len(rtrim(@p))-1) else @p END -- check if we have managed to get authors. If not - try to get it from the title table IF @r IS NULL BEGIN SELECT @r = a.descAuthor FROM Author a, Title_has_author tha WHERE a.idAuthor = tha.author AND tha.title = @idTitle END return @r END
Merge the two rows in one row   (192 Views)
Emp ----- ID Name 1 x 1 P 2 y 2 Q 3 W Emp_Data Id Name-1 Name-2 1 x P 2 y Q 3 w above is my tables (Emp ),And the I want the Resulting Data in the (Emp_Data) format.is this possible Pls Help.......
[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documen   (220 Views)
I had the same code ..it worked till sday and today i get this error [DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation. y IS IT SO please help meee
General Network Error: My code or the server?   (188 Views)
, this post will be a bit lengthy, so please bear with me. I've been programming asp.net for abou 2 years. I'm not a master by any means, and I do things like I learn from books. Here is my standard way of connection to my mssql database (which is on the same server as my site, so I just use "localhost") Dim objConnection As New SqlConnection(Application("dbstring")) Dim strSQL As String = "SELECT * FROM myTable WHERE this='that'" Dim objCommand As New SqlCommand(strSQL, objConnection) Dim objDataReader As SqlDataReader objConnection.Open() objDataReader = objCommand.ExecuteReader() Do While objDataReader.Read() = True 'Do what I need to do with the data Loop objDataReader.Close() objConnection.Close() objConnection.Dispose() Here's my issue. Our website gets this error 2-4 and as many as 6 or 7 times a day: General network error. Check your network documentation. Line ###: objConnection.Open() I use the method I wrote above for getting data on almost every page in the site. Its a HUGE site, but I've made sure that I close all my connections. I have been on goole for hours and hours trying to find a fix to this and there seems like so many different fixes. None of them have worked for us. This is my global connection string: Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs) Application("dbstring") = "Server=WWW1;database=###;uid=#####;password=##### ;Connect Timeout=300;Connection Reset=False;Pooling=False;Max Pool Size=75000;" End Sub Here's my web.config file: ... This server has only one website running on it, its a dedicated server. Is there something in my code causing this error to happen so much We have to restart the sql server everytime this error happens for it to go away. Its very disruptive to the website.
Stored Procedures - one form into two tables   (295 Views)
Code: CREATE PROCEDURE dbo.spAddShippingAddress (@OneA varchar(50), @TwoA varchar(50)) AS INSERT INTO (aXOne (OneA), aXTwo (TwoA)) VALUES (@OneA, @TwoA) GO I'm having problems with the stored procedure above. I have one form and I want to update two tables.
Query two tables with related data   (169 Views)
i have this query select distinct drawer, packagetype, doctype from document where drawer = 'abul' returns drawer-packtype-doctype ABUL 1000 0 ABUL 1015 0 ABUL 1017 0 ABUL 9999 NOTE ABUL 1000 RECO ABUL 1000 MISC ABUL 1000 EMAI ABUL 1000 TEST ABUL 1000 FX i have another table named packtype. it has columns packagetype & description. Instead of showing the packtype in the original query, i want to show the description from the packtype table.
Problems with slight differences in two 'duplicate' records   (178 Views)
I am having a tricky problem. I have records in a database that are in pairs based on a composite key (3 fields). Some of the pairs are completely identical, meaning each and every field in those pairs are the same. Some, however have a slight difference, in that one of the fields was updated and does not make the pairs truly identical. Let's assume a table like this: Table Name - FOOD Fields: (RecID, Name, Type, Color, Size) And the table structure is laid out like so: 1, Apple, Fruit, Red, Small 1, Apple, Fruit, Green, Small 2, Grape, Fruit, Purple, Small 2, Grape, Fruit, Purple, Small 3, Pineapple, Fruit, Brown, Small 3, Pineapple, Fruit, Brown, Large What I want to do is write a query that will show me only the pairs that have changes in them. When I run the query I would want to see the following: 1, Apple, Fruit, Red, Small 1, Apple, Fruit, Green, Small 3, Pineapple, Fruit, Brown, Small 3, Pineapple, Fruit, Brown, Large I would not want to see #2 (Grapes) because the records are identical. Might I have to use a self join
Selecting two values for one operator.   (181 Views)
Im a doofus. Im trying to right a SQL select statement where i need to pull records that have two different types of criteria for one selector. Code: objRs.Open "SELECT ProdID, ProdURL, ProdSKU, Category, ThumbImageURL, LinkText, ProdURL, ProdMiniDescription FROM bproducts WHERE Manufacturer='6' and Category='part,processor'" , objCn, 1, 3 This obviously doesnt work though. Any Ideas
Delete from two tables   (166 Views)
These are my selcet and delete commands, however I am trying to delete from two tables both Assets and AssetAttribute. The both have related colums AssetTypeId, AssetAttributeId. I need to slect them first then delete them. I need the one action delete to delete int eh same information from both tables. Help. I only have the one written for Asst table, I need to include the Assetattribute table. Please help me. SelectCommand="SELECT AssetId, AssetTypeId, Description, AssetAttributeId, SKU, Barcode, GovBarcode, WarehouseId, IsVehicle, DeploymentStatus FROM Asset, AssetAttribute WHERE AssetId = @AssetId" DeleteCommand="DELETE FROM Asset, AssetAttribute WHERE AssetId = @AssetId"
Compare two tables   (157 Views)
I'm working with sql servre database these are tables in my databas. Code: CREATE TABLE [dbo].[JobSeekerEducation] ( [JobSeekerEducationID] [bigint] IDENTITY (1, 1) NOT NULL , [JobSeekerID] [bigint] NULL , [EducationTypeID] [tinyint] NULL , [EducationCourseID] [smallint] NULL , ) ON [PRIMARY] GO CREATE TABLE [dbo].[JobSeekerSkill] ( [JobSeekerSkillID] [bigint] IDENTITY (1, 1) NOT NULL , [JobSeekerID] [bigint] NULL , [SkillCategoryID] [smallint] NULL , [SkillID] [smallint] NULL , ) ON [PRIMARY] GO CREATE TABLE [dbo].[JobSeeker] ( [JobSeekerID] [bigint] IDENTITY (1, 1) NOT NULL , [FirstName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL , [Surname] [varchar] (100) COLLATE Latin1_General_CI_AS NULL , [FullName] [varchar] (200) COLLATE Latin1_General_CI_AS NULL , [KnownAs] [varchar] (100) COLLATE Latin1_General_CI_AS NULL , [DateOfBirth] [smalldatetime] NULL , [HomeAddressLine1] [varchar] (100) COLLATE Latin1_General_CI_AS NULL , [HomeAddressLine2] [varchar] (100) COLLATE Latin1_General_CI_AS NULL , [HomeCity] [varchar] (100) COLLATE Latin1_General_CI_AS NULL , [HomeRegion] [varchar] (100) COLLATE Latin1_General_CI_AS NULL , [HomePostalcode] [varchar] (20) COLLATE Latin1_General_CI_AS NULL , [HomeCountryID] [smallint] NULL , [EmailAddress] [varchar] (200) COLLATE Latin1_General_CI_AS NULL , [HomeTelephone] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , [WorkTelephone] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , [Mobilephone] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , [HomeFax] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , [ContactMethod1ID] [tinyint] NULL , [ContactMethod2ID] [tinyint] NULL , [PreferredEmploymentTypeID1] [tinyint] NULL , [PreferredEmploymentTypeID2] [tinyint] NULL , [PreferredJobSectorID1] [smallint] NULL , [PreferredJobSectorID2] [smallint] NULL , [PreferredCountryLocationID1] [smallint] NULL , [PreferredCountryLocationID2] [smallint] NULL , [PreferredOrganisationTypeID1] [tinyint] NULL , [PreferredOrganisationTypeID2] [tinyint] NULL , [PreferredJobRoleID1] [smallint] NULL , [PreferredJobRoleID2] [smallint] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[t_JobVacancy] ( [JobVacancyID] [bigint] IDENTITY (1, 1) NOT NULL , [ReferenceCode] [varchar] (10) COLLATE Latin1_General_CI_AS NULL , [JobSectorID] [smallint] NULL , [JobRoleID] [smallint] NULL , [OtherJobRole] [varchar] (100) COLLATE Latin1_General_CI_AS NULL , [NumberOfVacancies] [tinyint] NULL , [JobVacancyStatusID] [tinyint] NULL , [OrganisationID] [bigint] NULL , [EmploymentTypeID] [tinyint] NULL , [CountryLocationID] [smallint] NULL , [VacancyPostedDate] [smalldatetime] NULL , [VacancyExpiryDate] [smalldatetime] NULL , [JobStartDate] [smalldatetime] NULL , [RecruitmentAgentID] [bigint] NULL , [SalaryRangeID] [tinyint] NULL , [SalaryRate] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , [Benefits] [varchar] (1000) COLLATE Latin1_General_CI_AS NULL , [RequiredExperienceLevel] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , [JobDescription] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL , [EducationTypeID1] [tinyint] NULL , [EducationCourseID1] [smallint] NULL , [EducationTypeID2] [tinyint] NULL , [EducationCourseID2] [smallint] NULL , [EducationTypeID3] [tinyint] NULL , [EducationCourseID3] [smallint] NULL , [EducationTypeID4] [tinyint] NULL , [EducationCourseID4] [smallint] NULL , [EducationTypeID5] [tinyint] NULL , [EducationCourseID5] [smallint] NULL , [SkillCategoryID1] [smallint] NULL , [SkillID1] [smallint] NULL , [SkillCategoryID2] [smallint] NULL , [SkillID2] [smallint] NULL , [SkillCategoryID3] [smallint] NULL , [SkillID3] [smallint] NULL , [SkillCategoryID4] [smallint] NULL , [SkillID4] [smallint] NULL , [SkillCategoryID5] [smallint] NULL , [SkillID5] [smallint] NULL , [Comments] [varchar] (1000) COLLATE Latin1_General_CI_AS NULL ) ON [PRIMARY] GO view: Code: CREATE VIEW viewJobApplication AS SELECT DISTINCT JobSeeker.FullName,JobSeeker.DateOfBirth,JobSeeker.HomeAddressLine1,JobSeeker.HomeAddressLine2,JobSeeker.HomeCity,JobSeeker.HomeRegion,JobSeeker.HomePostalcode,JobSeeker.HomeTelephone,JobSeeker.WorkTelephone,JobSeeker.Mobilephone, JobSeeker.HomeFax, JobSeeker.EmailAddress,JobSeeker.ContactMethod1ID, JobSeeker.ContactMethod2ID,JobSeeker.JobSeekerID,JobSeeker.PreferredEmploymentTypeID1,JobSeeker.PreferredEmploymentTypeID2,JobSeeker.PreferredJobSectorID1,JobSeeker.PreferredJobSectorID2, JobSeeker.PreferredCountryLocationID1,JobSeeker.PreferredCountryLocationID2,JobSeeker.PreferredOrganisationTypeID1, JobSeeker.PreferredOrganisationTypeID2,JobSeeker.PreferredJobRoleID1,JobSeeker.PreferredJobRoleID2,JobSeekerEducation.EducationTypeID,JobSeekerEducation.EducationCourseID, JobSeekerEducation.OtherEducationCourse,JobSeekerSkill.SkillCategoryID,JobSeekerSkill.SkillID FROM JobSeeker INNER JOIN JobSeekerEducation ON JobSeekerEducation.JobSeekerID = JobSeeker.JobSeekerID INNER JOIN JobSeekerSkill ON JobSeekerSkill.JobSeekerID = JobSeeker.JobSeekerID GO I want to compare values in t_JobVacancy table and viewJobApplication view if values are eual I want to insert values to Score feild.according to that score I want to orderby JobSeekerID and FullName.I managed to write a query, Code: select DISTINCT JobSeekerID,FullName,DateOfBirth,HomeAddressLine1,HomeAddressLine2,HomeCity,HomeRegion,HomePostalcode,HomeTelephone,WorkTelephone,Mobilephone, HomeFax, EmailAddress,ContactMethod1ID, ContactMethod2ID, case when a.PreferredEmploymentTypeID1 = j.EmploymentTypeID then 1 else 0 end +case when a.PreferredEmploymentTypeID2 = j.EmploymentTypeID then 1 else 0 end +case when a.PreferredJobSectorID1= j.JobSectorID then 1 else 0 end +case when a.PreferredJobSectorID2= j.JobSectorID then 1 else 0 end +case when a.PreferredCountryLocationID1= j.CountryLocationID then 1 else 0 end +case when a.PreferredCountryLocationID2= j.CountryLocationID then 1 else 0 end +case when a.EducationTypeID= j.EducationTypeID1 then 1 else 0 end +case when a.EducationTypeID= j.EducationTypeID2 then 1 else 0 end +case when a.EducationTypeID= j.EducationTypeID3 then 1 else 0 end +case when a.EducationTypeID= j.EducationTypeID4 then 1 else 0 end +case when a.EducationTypeID= j.EducationTypeID5 then 1 else 0 end +case when a.EducationCourseID= j.EducationCourseID1 then 1 else 0 end +case when a.EducationCourseID= j.EducationCourseID2 then 1 else 0 end +case when a.EducationCourseID= j.EducationCourseID3 then 1 else 0 end +case when a.EducationCourseID= j.EducationCourseID4 then 1 else 0 end +case when a.EducationCourseID= j.EducationCourseID5 then 1 else 0 end +case when a.SkillCategoryID= j.SkillCategoryID1 then 1 else 0 end +case when a.SkillCategoryID= j.SkillCategoryID2 then 1 else 0 end +case when a.SkillCategoryID= j.SkillCategoryID3 then 1 else 0 end +case when a.SkillCategoryID= j.SkillCategoryID4 then 1 else 0 end +case when a.SkillCategoryID= j.SkillCategoryID5 then 1 else 0 end as score FROM viewJobApplication a INNER JOIN t_JobVacancy j ON a.PreferredJobRoleID1=j.JobRoleID OR a.PreferredJobRoleID2=j.JobRoleID where j.JobVacancyID=1 order by score desc my problem is I get more than one score for same jobseekrID how can I solove this.
Delete records from two tables   (155 Views)
I have two tables one called students and location. I want to delete the location and all records in student corrosponding to that location, can any help or point me in the right direction. Many