get all data associated with one record in another...
-
AND only display the information of the one record once.
Basically here is the tables I am working with
CREATE TABLE [dbo].[booth] (
[booth_id] [int] IDENTITY (1, 1) NOT NULL ,
[booth_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[branch_id] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[station] (
[station_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[num] [int] NOT NULL ,
[booth_id] [int] NOT NULL ,
[info_id] [bigint] NOT NULL ,
[data_id] [bigint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[station_data] (
[data_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[terminal] [bit] NOT NULL ,
[radio] [bit] NOT NULL ,
[nextel] [bit] NOT NULL ,
[battery] [bit] NOT NULL ,
[motorcycle] [bit] NOT NULL ,
[shop] [bit] NOT NULL ,
[in_service] [bit] NOT NULL ,
[porsche] [bit] NOT NULL ,
[med_duty] [bit] NOT NULL ,
[heavy_duty] [bit] NOT NULL ,
[dollies] [bit] NOT NULL ,
[skates] [bit] NOT NULL ,
[go_jacks] [bit] NOT NULL ,
[extended_cab] [bit] NOT NULL ,
[rates] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[station_driver] (
[driver_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[station_id] [bigint] NOT NULL ,
[name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[alt_phone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contact_order] [int] NOT NULL ,
[truck_id] [bigint] NULL ,
[nextel_id] [bigint] NULL ,
[comment_id] [bigint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[station_nextel] (
[nextel_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[station_id] [bigint] NOT NULL ,
[num] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[station_truck] (
[truck_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[station_id] [bigint] NOT NULL ,
[type_id] [int] NOT NULL ,
[comm_id] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[station_truck_type] (
[type_id] [int] IDENTITY (1, 1) NOT NULL ,
[truck_type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
Here is the query I have so far
SELECT s.num, sd.terminal, sd.radio, sd.nextel, sd.battery, sd.motorcycle, sd.shop,
sd.in_service, sd.porsche, sd.med_duty, sd.heavy_duty, sd.dollies,
sd.go_jacks, sd.extended_cab, sdr.name, sdr.phone, sdr.alt_phone,
sdr.contact_order, sdr.driver_id, stt.truck_type, sn.num AS nextelNum, s.station_id,
sd.skates, sd.rates
FROM dbo.station s INNER JOIN
dbo.station_data sd ON s.data_id = sd.data_id RIGHT OUTER JOIN
dbo.station_driver sdr ON s.station_id = sdr.station_id AND sdr.contact_order <> 0 LEFT OUTER JOIN
dbo.station_truck st ON sdr.truck_id = st.truck_id LEFT OUTER JOIN
dbo.station_truck_type stt ON st.type_id = stt.type_id LEFT OUTER JOIN
dbo.station_nextel sn ON sdr.nextel_id = sn.nextel_id
ORDER BY s.num
And it gives me this
9801,True,False,False,False,True,True,True,True,True,False,False,False,False,"Shop","908-459-5177","",1,3,"","",2,False,"60 Hook"
9810,False,False,False,False,False,True,True,False,True,False,False,False,False,"Shop","908-689-0141","",1,1,"","",1,False,""
9810,False,False,False,False,False,True,True,False,True,False,False,False,False,"Darryl","908-966-1476","",2,2,"","",1,False,""
You can see that it gives me the date for station number 9810 twice because there are 2 drivers on for them.
I want it to return both drivers so I can ouptut them in a grid like this
9801 driver 1
9810 driver 1 driver 2
So if they have only 1 driver it will be fine and if they have more then one I can get the data and just output on the same row.
-
You need something like this :
IF Object_id('ListTableColumns') > 0
DROP FUNCTION ListTableColumns
GO
CREATE FUNCTION dbo.ListTableColumns (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + C.Name + ', '
FROMdbo.SysColumns C
WHEREC.id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
ORDER BYC.Name
SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))
RETURN @Items
END
GO
Select dbo.ListTableColumns(Object_id('SysObjects'))
--base_schema_ver, cache, category, crdate, deltrig, ftcatid, id, indexdel, info, instrig, name, parent_obj, refdate, replinfo, schema_ver, seltrig, stats_schema_ver, status, sysstat, type, uid, updtrig, userstat, version, xtype
DROP FUNCTION ListTableColumns
-
allright if you say so
Could you explain a little bit of what in sam hill that is doing?
-
It concatenates the columns names for each table in the db and presents it in a single row. You'll have to do something like this for the drivers, then call that function in the select to get that list.
-
alright man first off thanks for the help
and now sorry but I am not following at all - I get that this is a user defined function so I created it but I have no idea how I would use it to do what I need
Sorry but I will admit that what I am trying to do is well beyone my scope and knowledge.
-
What part are you not getting??
-
How to use the function to do what I need it to do
I place a table name wher eyou have systemObjects and I get the column names but I am not really sure what I need to do
I still get 3 rows of data when I only want 2
-
Forget the sysObjects table. This is just for a demo that'll work on any server.
The select will look something like this :
Select @Drivers = @Drivers + Name + ', ' from dbo.station_driver where id = @Station_Id --PassedInParameter
then just trim the last 2 characters (trailing comma).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply