SEARCH YOUR SOLUTION HERE  

Combining info from two tables?

, 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.

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




Related Messages:

two select statements on one table   (285 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 ?   (137 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   (117 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   (102 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   (151 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   (98 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
random display of information from databases   (190 Views)
can someone help me... this is my code but whenever i clicked refresh my item seem to become lesser... 'make it random Dim x, recordsCount Randomize 'first count how many records we have: recordsCount=0 Do Until rs.EOF recordsCount=recordsCount+1 : rs.MoveNext Loop rs.Close 'now execute again: set rs = cmd.Execute 'finally make it really random For x=1 To CLng(recordsCount * Rnd) rs.MoveNext Next
How to show more verbose information when running a ddl sql script in "Microsoft SQL   (100 Views)
Hi : I am using Microsoft Sql 2005. I ran my ddl scripts in "Microsoft SQL Server Management Studio" and when the script is completed and successfull, all I get is the following messages: If DBCC printed error messages, contact your system administrator. Is there a way to ask "Microsoft SQL Server Management Studio" to show more information or verbose I can use print statements but is it possible to ask the "Microsoft SQL Server Management Studio" to generate addtonal information when running the ddl sql scripts in "Microsoft SQL Server Management Studio". Yours, Frustrated.
[Help] Network SQL text   (109 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)   (104 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.
how to preserve a userid and password information   (112 Views)
Everyone, Using ASP I'm trying to logon to a "*.mdw" database. I'm successful there and once the connection is established I'm directing the user to another asp page to select the options they want. Can anyone tell me how do I preserve a userid and password across all the pages each time when a user is directed to different pages they view so that they don't have to logon again and again.
Merging two records from the same table in SELECT   (101 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
How to show more verbose information when running a ddl sql script in "Microsoft SQL   (91 Views)
Hi: I am using Microsoft Sql 2005 Server. I ran the ddl using "Microsoft Sql Server Management Studio". My ddl I used create Database, login, user, user mapping, tables,NONCLUSTERED index. I placed "PRINT" statements before and after each ddl. When I ran the ddl it print this statement: BEGIN Begin Step 1 Create Database It wait for a period of time and when it finish it print the below statements. Is it possible to make the print the statements print out as it run each step instead of printing out all of the statements when it is finish. DDL CODE: /** Begin Step 1 Create Database **/ PRINT 'BEGIN Begin Step 1 Create Database ' USE [master] GO /****** Object: Database [JOHNSMITH] Script Date: 08/26/2010 15:48:49 ******/ IF EXISTS (SELECT name FROM sys.databases WHERE name = N'JOHNSMITH') DROP DATABASE [JOHNSMITH] GO /****** Object: Login [jadeite100] Script Date: 08/26/2010 15:43:13 ******/ IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'jadeite100') DROP LOGIN [jadeite100] GO /****** Object: Database [JOHNSMITH] Script Date: 08/24/2010 14:01:33 ******/ CREATE DATABASE [JOHNSMITH] ON PRIMARY ( NAME = N'JOHNSMITH', FILENAME = N'c:\SQL\JOHNSMITH\JOHNSMITH.mdf' , SIZE = 1961856KB , MAXSIZE = UNLIMITED, FILEGROWTH = 20%) LOG ON ( NAME = N'JOHNSMITH_log', FILENAME = N'c:\SQL\JOHNSMITH\JOHNSMITH_log.ldf' , SIZE = 3460224KB , MAXSIZE = 2048GB , FILEGROWTH = 20%) COLLATE SQL_Latin1_General_CP1_CI_AS GO EXEC dbo.sp_dbcmptlevel @dbname=N'JOHNSMITH', @new_cmptlevel=90 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [JOHNSMITH].[dbo].[sp_fulltext_database] @action = 'disable' end GO ALTER DATABASE [JOHNSMITH] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [JOHNSMITH] SET ANSI_NULLS OFF GO ALTER DATABASE [JOHNSMITH] SET ANSI_PADDING OFF GO ALTER DATABASE [JOHNSMITH] SET ANSI_WARNINGS OFF GO ALTER DATABASE [JOHNSMITH] SET ARITHABORT OFF GO ALTER DATABASE [JOHNSMITH] SET AUTO_CLOSE OFF GO ALTER DATABASE [JOHNSMITH] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [JOHNSMITH] SET AUTO_SHRINK OFF GO ALTER DATABASE [JOHNSMITH] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [JOHNSMITH] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [JOHNSMITH] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [JOHNSMITH] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [JOHNSMITH] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [JOHNSMITH] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [JOHNSMITH] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [JOHNSMITH] SET ENABLE_BROKER GO ALTER DATABASE [JOHNSMITH] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [JOHNSMITH] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [JOHNSMITH] SET TRUSTWORTHY OFF GO ALTER DATABASE [JOHNSMITH] SET ALLOW_SNAPSHOT_ISOLATION ON GO ALTER DATABASE [JOHNSMITH] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [JOHNSMITH] SET READ_WRITE GO ALTER DATABASE [JOHNSMITH] SET RECOVERY FULL GO ALTER DATABASE [JOHNSMITH] SET MULTI_USER GO ALTER DATABASE [JOHNSMITH] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [JOHNSMITH] SET DB_CHAINING OFF GO PRINT 'End Begin Step 1 Create Database ' /** End Step 1 Create Database **/ /** Begin Step 2 Create Login **/ PRINT 'BEGIN Begin Step 2 Create Login ' /****** Object: Login [jadeite100] Script Date: 08/24/2010 15:31:32 ******/ /****** Object: Login [jadeite100] Script Date: 08/24/2010 15:31:32 ******/ CREATE LOGIN [jadeite100] WITH PASSWORD=N'mychau1', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON GO EXEC sys.sp_addsrvrolemember @loginame = N'jadeite100', @rolename = N'sysadmin' GO EXEC sys.sp_addsrvrolemember @loginame = N'jadeite100', @rolename = N'securityadmin' GO EXEC sys.sp_addsrvrolemember @loginame = N'jadeite100', @rolename = N'serveradmin' GO EXEC sys.sp_addsrvrolemember @loginame = N'jadeite100', @rolename = N'setupadmin' GO EXEC sys.sp_addsrvrolemember @loginame = N'jadeite100', @rolename = N'processadmin' GO EXEC sys.sp_addsrvrolemember @loginame = N'jadeite100', @rolename = N'diskadmin' GO EXEC sys.sp_addsrvrolemember @loginame = N'jadeite100', @rolename = N'dbcreator' GO EXEC sys.sp_addsrvrolemember @loginame = N'jadeite100', @rolename = N'bulkadmin' GO ALTER LOGIN [jadeite100] ENABLE GO PRINT 'END Begin Step 2 Create Login ' /** End Step 2 Create Login **/ PRINT 'Begin Step 3 Create User ' /** Begin Step 3 Create User **/ USE [JOHNSMITH] GO /****** Object: User [jadeite100] Script Date: 08/25/2010 13:26:14 ******/ GO CREATE USER [jadeite100] FOR LOGIN [jadeite100] WITH DEFAULT_SCHEMA=[dbo] PRINT 'End Step 3 Create User ' /** End Step 3 Create User **/ PRINT 'Begin Step 4 User Mapping ' /** Begin Step 4 User Mapping **/ use [JOHNSMITH] go exec sp_addrolemember N'db_accessadmin', jadeite100 go exec sp_addrolemember N'db_backupoperator', jadeite100 go exec sp_addrolemember N'db_datareader', jadeite100 go exec sp_addrolemember N'db_datawriter', jadeite100 go exec sp_addrolemember N'db_ddladmin', jadeite100 go exec sp_addrolemember N'db_denydatareader', jadeite100 go exec sp_addrolemember N'db_owner', jadeite100 PRINT 'End Step 4 User Mapping ' /** End Step 4 User Mapping **/ PRINT 'Begin Step 5 Create Tables ' /** Begin Step 5 Create Tables **/ USE [JOHNSMITH] GO /****** Object: Table [dbo].[test2] Script Date: 08/19/2010 14:05:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[test2]( [ID] [char](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [TYPE] [char](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [TEMPLATE_GROUP] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [TEMPLATE_FORM_NAME] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [XML_FORM_DATA] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [LAST_UPDATE_TS] [datetime] NOT NULL, [LAST_UPDATE_BY] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_GENERIC_FORM] PRIMARY KEY CLUSTERED ( [ID] ASC, [TYPE] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF USE [JOHNSMITH] GO /****** Object: Table [dbo].[test1] Script Date: 08/19/2010 14:06:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[test1]( [ID] [char](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [TYPE] [char](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [TEMPLATE_GROUP] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [TEMPLATE_FORM_NAME] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [XML_FORM_DATA] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [LAST_UPDATE_TS] [datetime] NOT NULL, [LAST_UPDATE_BY] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_FORM_DATA] PRIMARY KEY CLUSTERED ( [ID] ASC, [TYPE] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF PRINT 'End Step 5 Create Tables ' /** End Step 5 Create Tables **/ /** Begin Step 6 set ALLOW_SNAPSHOT_ISOLATION ON**/ PRINT 'Begin Step 6 set ALLOW_SNAPSHOT_ISOLATION ON ' ALTER DATABASE [JOHNSMITH] SET ALLOW_SNAPSHOT_ISOLATION ON; GO PRINT 'End Step 6 set ALLOW_SNAPSHOT_ISOLATION ON ' /** End Step 6 ALLOW_SNAPSHOT_ISOLATION ON **/ PRINT 'Begin Step 7 NONCLUSTERED INDEX test2 ' /** Begin Step 7 NONCLUSTERED INDEX test2**/ USE [JOHNSMITH] GO /****** Object: Index [IX_test2] Script Date: 08/23/2010 15:06:51 ******/ CREATE NONCLUSTERED INDEX [IX_test2] ON [dbo].[test2] ( [LAST_UPDATE_TS] ASC ) INCLUDE ( [ID], [TYPE]) WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]; PRINT 'End Step 7 NONCLUSTERED INDEX test2 ' /** End Step 7 NONCLUSTERED INDEX test2 **/ PRINT 'Begin Step 8 NONCLUSTERED INDEX test1 ' /** Begin Step 8 NONCLUSTERED INDEX test1 **/ USE [JOHNSMITH] GO /****** Object: Index [IX_test1] Script Date: 08/23/2010 15:11:02 ******/ CREATE NONCLUSTERED INDEX [IX_test1] ON [dbo].[test1] ( [LAST_UPDATE_TS] ASC ) INCLUDE ( [ID], [TYPE]) WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]; PRINT 'End Step 8 NONCLUSTERED INDEX test1 ' /** End Step 8 NONCLUSTERED INDEX test1**/ OUTPUT OF DDL: DBCC execution completed. If DBCC printed error messages, contact your system administrator. End Begin Step 1 Create Database BEGIN Begin Step 2 Create Login END Begin Step 2 Create Login Begin Step 3 Create User End Step 3 Create User Begin Step 4 User Mapping End Step 4 User Mapping Begin Step 5 Create Tables End Step 5 Create Tables Begin Step 6 set ALLOW_SNAPSHOT_ISOLATION ON End Step 6 set ALLOW_SNAPSHOT_ISOLATION ON Begin Step 7 NONCLUSTERED INDEX test2 End Step 7 NONCLUSTERED INDEX test2 Begin Step 8 NONCLUSTERED INDEX test1 End Step 8 NONCLUSTERED INDEX test1 Yours, Frustrated.
Merge the two rows in one row   (140 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.......
Import information into Active Directory From Data source   (144 Views)
I have a database (mySQL) that has users name, email, phone numbers, addresses, titles, departments, etc. It is for an online phone search that I set up for our users internally. The Admin here didn't really use AD to house that information (though he should have). Well, now we are trying to get "my" database to input data to Active Directory/LDAP/ADSI or whatever. There has GOT to be a way.... I have the data in mySQL, but we have SQL 2000 Server, We have Exchange 2000 running on Windows 2003 Server. I've been just grasping at straws left and right.... I'm trying to basically write a script, or app or hell, even a SQL Stored Proc to basically say "IF db.FirstName = ad.FirstName AND db.LastName = ad.LastName, Then set db.Title = ad.Title, db.Address = ad.Address and so on. Why isn't active directory housed in SQL any way Isn't that supposed to be happening in SQL 2005 and Vista I look forward to your responses.
Where can we get Service Pack release info ????   (135 Views)
Hi Everyone, Am new to SQL Server Administration. My question, is there is any standard official web-site where we can know all the service pack related information and what is the latest hot fix inofrmation , what is the latest SQL Server database version that Microsoft released. Basically am searching for the service packs in Google and that to if someone tells that SP1 has released for SQL 2008. As a DBA, just want to know where can we get this information so that we will be aware of Service Pack releases and so on ....
Storing more information   (99 Views)
I have a field which stores html and the is set to varchar 8000. I need to store more information, is there a way i can do this If not what would be the best way to go about doing this
Looping info into an SQL function   (94 Views)
Hi I need to get a list of numbers from an sql satement i.e SELECT name_ctr FROM actNamePropertyAddressView then I need to do a loop inserting the name_ctr value in the procedure below select dbo.funnucNamePostAddress(name_ctr), 'pr', 0, GETDATE()) then I need to output the results I would appreciate any feedback on this
help extracting user info   (161 Views)
please can anyone help me i have a ipb forum some how i accidently delete my post i have a old backup from 3 week ago how can i extract the post from the backup NOTE: the only post that was deleted was post made by me none of the other member post was deleted i did not want to restore the entire post table beacause i would loose post in the past 3 weeks can anyone help me with a script or anything i can used to extract post of the post table where the poster id is 1 here is a sample INSERT INTO `ibf_posts` VALUES (2, 0, NULL, 1, 'albertldee', 1, 1, '12.8.34.199' INSERT INTO `ibf_posts` VALUES (3, 0, NULL, 4, ' - ', 1, 1, '207.44.154.35 INSERT INTO `ibf_posts` VALUES (4, 0, NULL, 6, 'demarcus', 1, 1, '69.142.131.149 INSERT INTO `ibf_posts` VALUES (5, 0, NULL, 7, 'gottasteu', 1, 1, '65.12.169.41 the post table has about 122000 line like above i just want to extract all the line that has albertldee like line number 1
SQL Server info   (129 Views)
Hi... I'm looking for some information on how SQL Server and MySQL work when using ASP.NET (VB.NET). I've only used access but now need to learn this technology... If anyone could post a link to some information or tell me themselves then this would be appreciated ...