Help: create complex list from table query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help: create complex list from table query

Author

Topic

ME55AGE5
Starting Member

2 Posts

Posted - 2010-02-12 : 10:11:11

I want to create a list of all wells that have 2 or more wellbore_id when values for parent_wellbore_id that are null have been removed, anybody got any suggestions

EXCLUDE row for wellbore_id in list when wb.parent_wellbore_id IS NULL (TABLE1)

SELECT LIST of wellbore_id FROM TABLE1 WHERE wellbore_id >= 2

Create TABLE with this data

SELECT
wb.wellbore_id, we.well_id, wb.parent_wellbore_id, po.policy_id, pr.project_id, si.site_id
FROM
wb, we, si, pr, po
WHERE
(((wb.parent_wellbore_id IS NOT NULL))) AND ((we.well_id =
wb.well_id) AND (si.site_id = we.site_id) AND
(pr.project_id = si.project_id) AND (po.policy_id =
pr.policy_id))

Had thought about creating a table temp_cd_wellbore_parent_is_not_null and then do a count on wellbore id > 2 but not sure how to do this

create table temp_cd_wb_parent_is_not_null as (select * from wb where parent_wellbore_id is not null)

Can someone help please

TABLES

po
--------
policy_id

--------------------------
pr
-------
policy_id
project_id

--------------------------

si
----
project_id
site_id

--------------------------

we
-------
site_id
well_id

--------------------------

wb
--------
well_id
wellbore_id
parent_wellbore_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 10:23:35

do you mean this?


SELECT * INTO YourDestTable
FROM wb
WHERE wellbore_id >= 2
AND parent_wellbore_id IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ME55AGE5
Starting Member

2 Posts

Posted - 2010-02-12 : 10:56:09

A little more info with example of the data in the table

wellbore_id is a unique identifier and I am trying to obtain a count of the amount of times this appears under the associated well_id

For example

well_1 might have 3 wellbore id so this well_id should be stored
well_2 might have 2 wellbore id so this well_id should be stored
well_3 might have 1 wellbore id so this well_id should not be added to table

Would hope to end up with a table that contains a list of the well_id of the wells that have 2 or more wellbore associated to the well_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 11:06:02

[code]
SELECT well_id INTO YourDestTable
FROM wb
WHERE parent_wellbore_id IS NULL
GROUP BY well_id
HAVING COUNT(DISTINCT wellbore id) > 1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-12 : 13:35:38

[code]
SELECT well_id
INTO YourDestTable
FROM wb
WHERE CHECKSUM(parent_wellbore_id) = 2147483647
GROUP BY well_id
HAVING MIN(wellbore_id) < MAX(wellbore_id);
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources