get 5 rows from 2 unrelated tables (was "Complicated sql query.... need help bad

I am trying to write a query that queries 2 tables and gets back 5 rows. These two tables arent really related and the data I get back is different for either table.
The 5 rows that I want to get back are for the LATEST 5 DATES. The date field for one table (F_INSPECTIONS) is END_DATE and the date field for the other table (F_OCCURRENCES) is OCCURRRENCE_DATE.

I am writing a program to do this so if its absolutely impossible to implement this with sql code then a suggestion of how I might be able to go about doing it antoher way would help.

Table descriptions:

/****** Object: Table [dbo].[F_INSPECTIONS] Script Date: 2/8/2005 10:59:41 AM ******/
CREATE TABLE [dbo].[F_INSPECTIONS] (
[INSPECTION_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL ,
[INSPECTION_NAME] [varchar] (150) COLLATE SQL_Latin1_General_CP850_CI_AI NULL ,
[CAP_FACILITY_ID] [int] NOT NULL ,
[REG_SURR_ID] [smallint] NOT NULL ,
[START_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[INSP_UPDATED_ON] [datetime] NULL ,
[INSP_ORIGIN_ID] [tinyint] NULL ,
[INSP_TYPE_ID] [tinyint] NULL ,
[DAYS_SINCE_LAST] [smallint] NULL ,
[VIOLATION_COUNT] [smallint] NULL ,
[NON_COMPLIANCE_IND] [tinyint] NULL ,
[INSPECTION_COUNT] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[F_OCCURRENCES] (
[OCCURRENCE_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL ,
[CAP_FACILITY_ID] [int] NOT NULL ,
[OCCURRENCE_NM] [varchar] (150) COLLATE SQL_Latin1_General_CP850_CI_AI NULL ,
[OCCURRENCE_DATE] [datetime] NULL ,
[REG_SURR_ID] [smallint] NOT NULL ,
[REPORTED_DATE] [datetime] NULL ,
[ASSESSMENT_DATE] [datetime] NULL ,
[UPDATED_ON] [datetime] NULL ,
[ORIGIN_ID] [tinyint] NULL ,
[CATEGORY_ID] [tinyint] NULL ,
[OUTCOME_ID] [tinyint] NULL
) ON [PRIMARY]

I need to query from these 2 tables and get these columns back:Are you trying to get back only 5 rows(attempting to join 2 unrelated tables), or 5 rows from each table(candidate for a UNION)?|||If I understood well, you'll need something like this: first "join" similar results from both tables using the UNION, and then select first five of them.SELECT ID, dat
FROM (SELECT ins_id ID, end_date dat
FROM F_INS
UNION
SELECT occ_id ID, occ_date dat
FROM F_OCC
ORDER BY 2)
WHERE ROWNUM <= 5
ORDER BY 2;Selecting "top 5" rows can be done in different ways; this one (using the ROWNUM pseudocolumn) is used in Oracle. I *think* MySQL has "SELECT TOP 5 ..." statement; I don't know which database engine you use (is it Access?), but I hope my example will help you find the result.|||This is exacly the logic I was looking for (top 5 dates out of the entire set) Hopefully a union like this works in SQL SERVER.

Thanks so much.|||My translation of the Oracle syntax into SQL-92 (which should run on MS-SQL 2000) would be:SELECT TOP 5 ID, dat
FROM (
SELECT ins_id ID, end_date dat
FROM F_INS
UNION SELECT occ_id ID, occ_date dat
FROM F_OCC) AS A
ORDER BY 2-PatP|||Here is the statement I wrote using that same logic. It works great however. i cant get the top 5 to work.

(select
F_OCCURRENCES.CAP_FACILITY_ID,
F_OCCURRENCES.REG_SURR_ID as reg_surr_id,
'N/A' as INPECTOR,
'N/A' as COMPLIANCE_STATUS,
'OCCURRENCE' as ACTIVITY,
D_OCCURRENCE_OUTCOME.OUTCOME_ENG_DESC as OUTCOME,
D_REGULATION.REG_ENGLISH_DESC AS REGULATION,
F_OCCURRENCES.OCCURRENCE_DATE as theDATE
from F_OCCURRENCES INNER JOIN D_OCCURRENCE_OUTCOME ON F_OCCURRENCES.OUTCOME_ID = D_OCCURRENCE_OUTCOME.OUTCOME_ID INNER JOIN D_REGULATION ON D_REGULATION.REG_SURR_ID = F_OCCURRENCES.REG_SURR_ID where cap_facility_id = '11518' and F_OCCURRENCES.REG_SURR_ID = '101'
UNION
SELECT
F_INSPECTIONS.CAP_FACILITY_ID,
F_INSPECTIONS.REG_SURR_ID as reg_surr_id,
'NO DATA' as INSPECTOR,
CASE Non_COMPLIANCE_IND WHEN 1 THEN 'Non-Compliant' WHEN 0 THEN 'Compliant' END as COMPLIANCE_STATUS,
'INSPECTION' as ACTIVITY,
DISP_ENGLISH_DESC as OUTCOME,
D_REGULATION.REG_ENGLISH_DESC AS REGULATION,
F_INSPECTIONS.START_DATE as theDATE
FROM F_INSPECTIONS LEFT JOIN F_VIOLATIONS ON F_INSPECTIONS.INSPECTION_ID = F_VIOLATIONS.INSPECTION_ID Left Join D_DISPOSITION on D_DISPOSITION.VIOLATION_DISP_ID = F_VIOLATIONS.VIOLATION_DISP_ID LEFT JOIN D_REGULATION ON F_INSPECTIONS.REG_SURR_ID = D_REGULATION.REG_SURR_ID where F_INSPECTIONS.REG_SURR_ID = '101' and F_INSPECTIONS.CAP_FACILITY_ID = '11518'
)
order by theDate desc|||This is just a "shot in the dark" since I'm not willing to take the time to analyze this SQL at the moment, but if that SQL works then I'd suggest:SELECT TOP 5 *
FROM (
SELECT
F_OCCURRENCES.CAP_FACILITY_ID
, F_OCCURRENCES.REG_SURR_ID as reg_surr_id
, 'N/A' as INPECTOR
, 'N/A' as COMPLIANCE_STATUS
, 'OCCURRENCE' as ACTIVITY
, D_OCCURRENCE_OUTCOME.OUTCOME_ENG_DESC as OUTCOME
, D_REGULATION.REG_ENGLISH_DESC AS REGULATION
, F_OCCURRENCES.OCCURRENCE_DATE as theDATE
FROM F_OCCURRENCES
INNER JOIN D_OCCURRENCE_OUTCOME
ON F_OCCURRENCES.OUTCOME_ID = D_OCCURRENCE_OUTCOME.OUTCOME_ID
INNER JOIN D_REGULATION
ON D_REGULATION.REG_SURR_ID = F_OCCURRENCES.REG_SURR_ID
WHERE cap_facility_id = '11518'
and F_OCCURRENCES.REG_SURR_ID = '101'
UNION SELECT
F_INSPECTIONS.CAP_FACILITY_ID
, F_INSPECTIONS.REG_SURR_ID as reg_surr_id
, 'NO DATA' as INSPECTOR
, CASE Non_COMPLIANCE_IND
WHEN 1 THEN 'Non-Compliant'
WHEN 0 THEN 'Compliant'
END as COMPLIANCE_STATUS
, 'INSPECTION' as ACTIVITY
, DISP_ENGLISH_DESC as OUTCOME
, D_REGULATION.REG_ENGLISH_DESC AS REGULATION
, F_INSPECTIONS.START_DATE as theDATE
FROM F_INSPECTIONS
LEFT JOIN F_VIOLATIONS
ON F_INSPECTIONS.INSPECTION_ID = F_VIOLATIONS.INSPECTION_ID
LEFT JOIN D_DISPOSITION
on D_DISPOSITION.VIOLATION_DISP_ID = F_VIOLATIONS.VIOLATION_DISP_ID
LEFT JOIN D_REGULATION
ON F_INSPECTIONS.REG_SURR_ID = D_REGULATION.REG_SURR_ID
where F_INSPECTIONS.REG_SURR_ID = '101'
and F_INSPECTIONS.CAP_FACILITY_ID = '11518'
) AS z
ORDER BY theDate desc-PatP|||Brilliant!
the "as z" is what made this work.

thanks very much|||That need for an alias bytes a lot of people... Standard SQL-92 isn't always as forgiving as Oracle (or any other vendor) can be, but that's the price you pay for portability.

-PatPsql

Generating database schema

Is it possible to take an exisiting database and write to a text file the sql schema for the database.

I think you can use SQL server 2005 and export the database schema as a ddl, but I need to do this on SQL server 2000 and preferably c#/vb or a 3rd party control (something I can send to a client to get a copy of there database schema)

Any help or ideas, much appreciated

Thanks

Scott Taylor

You might want to refer to this article: http://www.karaszi.com/SQLServer/info_generate_script.asp -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <Scott Taylor2@.discussions.microsoft.com> wrote in message news:01811438-cdd4-4d6f-b2c7-234f99dd5f38@.discussions.microsoft.com... Is it possible to take an exisiting database and write to a text file the sql schema for the database. I think you can use SQL server 2005 and export the database schema as a ddl, but I need to do this on SQL server 2000 and preferably c#/vb or a 3rd party control (something I can send to a client to get a copy of there database schema) Any help or ideas, much appreciated Thanks Scott Taylor

generate update statements for existing data

Does anyone know how to write scripts for generating update statements for
existing data?
I found a stored procedure online that generates INSERT statements for a
given table, I was wondering if anyone has worked on a UPDATE generatorMike
What do you mean by "generating update"?
Do you want to build a script that does updateting?
declare @.sql varchar(400)
set @.sql='update '+ @.tablename+' set col'..........
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:9E10B2B9-80CC-4E50-9E34-5865A0C3BA1D@.microsoft.com...
> Does anyone know how to write scripts for generating update statements for
> existing data?
> I found a stored procedure online that generates INSERT statements for a
> given table, I was wondering if anyone has worked on a UPDATE generator|||I have a tables with about 1000 records and if I make changes I would like t
o
generate script (UPDATE scripts) for the existing data that I could run on
another server or ship it to a client with the latest changes instead of a
restore.
there is a good insert script available at
http://vyaskn.tripod.com/code.htm#inserts , I am looking for something
similar for UPDATE
"Uri Dimant" wrote:

> Mike
> What do you mean by "generating update"?
> Do you want to build a script that does updateting?
>
> declare @.sql varchar(400)
> set @.sql='update '+ @.tablename+' set col'..........
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:9E10B2B9-80CC-4E50-9E34-5865A0C3BA1D@.microsoft.com...
>
>|||Mike
Do you consider using Replication/Triggers ? It will be much better than
generating SQL script.
If you use SQL Server 2005 you can create a SNAPSHOP database ( ah i forgot
, it should be on the same server),sorry.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:00EF43C1-EBF7-4422-99D0-A1979F3500A0@.microsoft.com...
>I have a tables with about 1000 records and if I make changes I would like
>to
> generate script (UPDATE scripts) for the existing data that I could run on
> another server or ship it to a client with the latest changes instead of a
> restore.
> there is a good insert script available at
> http://vyaskn.tripod.com/code.htm#inserts , I am looking for something
> similar for UPDATE
> "Uri Dimant" wrote:
>|||Mike wrote:
> I have a tables with about 1000 records and if I make changes I would like
to
> generate script (UPDATE scripts) for the existing data that I could run on
> another server or ship it to a client with the latest changes instead of a
> restore.
> there is a good insert script available at
> http://vyaskn.tripod.com/code.htm#inserts , I am looking for something
> similar for UPDATE
>
http://www.red-gate.com/products/SQ...mpare/index.htm
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Try www.sqlscripter.com to generate data scripts. All types are supported
(Insert, Update, Delete).
It's free.
Thomas
"Mike" wrote:

> Does anyone know how to write scripts for generating update statements for
> existing data?
> I found a stored procedure online that generates INSERT statements for a
> given table, I was wondering if anyone has worked on a UPDATE generator

Generate Report Query

Hi every body
the image below describe entity relation diagram of my database i need to Write Query the generate report of licensed buildings (that has license saved in BuildingLicense Table ) and not licensed buildings in section or street

i need to gerate report like table below

select
B.streat,
Licensed = sum(case when BL.LicNo is not null then 1 else 0 end),
NotLicensed = sum(case when BL.LicNo is null then 1 else 0 end)
from
Building B (nolock)
left outer join BuildingLicensce BL (nolock)
on BL.Building_NO = B.BuildingNo
group by
B.streat

if you also what the Serial Number column, create a temp table with identity columns and dump above results intot the temp table.

|||Thanks MR

Lakshmana Kumar K for your interest
i tried you query
in my databast i stored 5 records in buildings table and when i run query i have this result

in result the sum of licensed and not licensed buildings give the total number of buildings
in your query it gives 8 Why?

I understoond your Query
in your Query u get the count of Licensecs for buildings in the street
but i just need the number of buildings which have license Record not the Count of Licenses
|||Finally i find the Answer

-- =============================================
-- Create Report of Licensed And Notlicensed Buildings in each Street
-- Auther Mahmoud Abd El Hakeem 09/02/2007
-- =============================================
IF EXISTS (SELECT name FROM sysobjects
WHERE name = N'LicBuilPerStreet'
AND type = 'U')
DROP TABLE [LicBuilPerStreet]
GO
SELECT Street.id AS [Street],
[Count Of Licensed Buildings]=
(
SELECT count(BuildingNO)
FROM Buildings
WHERE
Buildings.Street = Street.id
AND
buildingNo IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
,
[Count Of Not Licensed Buildings] =
(
SELECT count(buildingNO)
FROM Buildings
WHERE
(
Buildings.street =Street.id
AND
buildingNo NOT IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
) INTO [LicBuilPerStreet]
FROM
Street
LEFT OUTER JOIN Buildings
ON Buildings.street = Street.id

GROUP BY Street.id

-- IF U NEED TO HIDE NULL VALUES MAKE IT RIGHT OUTER JOIN

SELECT [street].[name],[LicBuilPerStreet].[Count Of Licensed Buildings],
[LicBuilPerStreet]. [Count Of Not Licensed Buildings]
FROM [LicBuilPerStreet]
INNER JOIN Street ON
[LicBuilPerStreet].street=Street.id
--WHERE street.secid= 15
ORDER BY Street.[Name]

and this is the result

i create temp table [LicBuilPerStreet]|||Finally i find the Answer

-- =============================================
-- Create Report of Licensed And Notlicensed Buildings in each Street
-- Auther Mahmoud Abd El Hakeem 09/02/2007
-- =============================================
IF EXISTS (SELECT name FROM sysobjects
WHERE name = N'LicBuilPerStreet'
AND type = 'U')
DROP TABLE [LicBuilPerStreet]
GO
SELECT Street.id AS [Street],
[Count Of Licensed Buildings]=
(
SELECT count(BuildingNO)
FROM Buildings
WHERE
Buildings.Street = Street.id
AND
buildingNo IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
,
[Count Of Not Licensed Buildings] =
(
SELECT count(buildingNO)
FROM Buildings
WHERE
(
Buildings.street =Street.id
AND
buildingNo NOT IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
) INTO [LicBuilPerStreet]
FROM
Street
LEFT OUTER JOIN Buildings
ON Buildings.street = Street.id

GROUP BY Street.id

-- IF U NEED TO HIDE NULL VALUES MAKE IT RIGHT OUTER JOIN

SELECT [street].[name],[LicBuilPerStreet].[Count Of Licensed Buildings],
[LicBuilPerStreet]. [Count Of Not Licensed Buildings]
FROM [LicBuilPerStreet]
INNER JOIN Street ON
[LicBuilPerStreet].street=Street.id
--WHERE street.secid= 15
ORDER BY Street.[Name]

and this is the result

i create temp table [LicBuilPerStreet]
Thanks MR

Lakshmana Kumar K 4 your Interest