Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old October 7th, 2003, 01:02 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default data retrieve from three tables

How can retrieve the data from three tables ?

Table1 stations column name stn_no, rec_id
Table2 software column name stn_no, rec_id, software
Table3 softwarelist colume name version, rec_id

rec_id means record id.
I could not retrieve the actual version of each software from table,
because in third table ie. Softwarelist there is stn_no column not exit.

Data like this

Stn_no rec_id software version
---- ------- -------- ------------
Comp1 1 Windows XP 1ST edition
Comp1 2 Windows 2000 2nd edition
Comp1 3 Windows 98 2nd editon
Comp2 4 Adobe 1st editon
Comp2 5 AutoCAD 2nd edition
Comp3 6 3D Studio MAX 4.2
Comp3 7 ARC View 8
………

I use following query

select stations.rec_id,stations.stn_no,softwarelist.rec_i d,software.software,softwarelist.version from stations,software,softwarelist where stations.stn_no=software.stn_no and stations.rec_id=softwarelist.rec_id and stations.stn_no='comp1'

problem in version column, it is display same version of each software why ?
it should display software’s and it separate version


Mateen
  #2 (permalink)  
Old October 7th, 2003, 04:05 AM
Authorized User
 
Join Date: May 2003
Location: Delhi, Delhi, India.
Posts: 62
Thanks: 0
Thanked 0 Times in 0 Posts
Default



Try to do the mapping between all the three table using rec_id like this:
select stations.rec_id,stations.stn_no,
softwarelist.rec_id,software.software,softwarelist .version
from stations,software,softwarelist
 where stations.rec_id=software.rec_id
and stations.rec_id=softwarelist.rec_id


Deepesh Jain
VB,VBA & .NET Specialist
Wiley Support Team
  #3 (permalink)  
Old October 7th, 2003, 04:26 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks for response.

by mapping it display only one record.
I have to display number of records ie softwares and their version
one comp1 have many softwares and each software separate version.

any other idea ?

Mateen



Quote:
quote:Originally posted by Deepesh_Jain



Try to do the mapping between all the three table using rec_id like this:
select stations.rec_id,stations.stn_no,
softwarelist.rec_id,software.software,softwarelist .version
from stations,software,softwarelist
where stations.rec_id=software.rec_id
and stations.rec_id=softwarelist.rec_id


Deepesh Jain
VB,VBA & .NET Specialist
Wiley Support Team


Similar Threads
Thread Thread Starter Forum Replies Last Post
Retrieve records from 2 tables snufse SQL Server 2000 7 January 29th, 2008 04:31 AM
retrieve data dynamically pitiporn Classic ASP Basics 3 May 28th, 2006 11:21 PM
cannot retrieve data because of redirecting artuc General .NET 0 December 18th, 2004 02:52 AM
Retrieve data Snib C++ Programming 2 November 6th, 2004 11:09 AM
Qry to retrieve from 2 tables and display tsimsha Classic ASP Databases 3 October 29th, 2004 02:03 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.