Sql-server – SQL query not selecting records

sql server

I'm trying to select * records. The problem is some only have values ('MNT', 'NPT', 'OM') for attrib_type 1-3 in this case. Attribute types 4 & 5 do not exist in the older records therefore there are 4500 records that are not being selected. I would like to be able to select ALL records even where there is no attrib_type 4 & 5 value.

The SQL I have tried is below

SELECT
   central_site.site_code,
   central_site.site_name,
   ward.ward_name,
   area.area_name,
   feature_type.feature_type_code,
   feature_type.feature_type_name,
   feature.feat_cent_east,
   feature.feat_cent_north,
   feat_measurement.feature_quantity,
   feat_attrib_type.attrib_value_code,
   feat_attrib_type_2.attrib_value_code,
   feat_attrib_type_3.attrib_value_code,
   feat_attrib_type_4.attrib_value_code,
   feat_attrib_type_5.attrib_value_code
FROM
   area,
   central_site,
   feat_attrib_type,
   feat_attrib_type feat_attrib_type_2,
   feat_attrib_type feat_attrib_type_3,
   feat_attrib_type feat_attrib_type_4,
   feat_attrib_type feat_attrib_type_5,
   feat_measurement,
   feature,
   feature_type,
   site,
   ward
WHERE
   central_site.site_code = site.site_code AND
   site.site_code = feature.site_code AND
   ward.ward_code = feature.ward_code AND
   area.area_code = feature.area_code AND
   feature_type.feature_type_code = feature.feature_type_code AND
   feature.site_code = feat_attrib_type.site_code AND
   feature.plot_number = feat_attrib_type.plot_number AND
   (feat_attrib_type.attrib_type_code = 'MNT' OR feat_attrib_type.attrib_type_code IS NULL) AND
   feature.site_code = feat_attrib_type_2.site_code AND
   feature.plot_number = feat_attrib_type_2.plot_number AND
   (feat_attrib_type_2.attrib_type_code = 'NPT' OR feat_attrib_type_2.attrib_type_code IS NULL) AND
   feature.site_code = feat_measurement.site_code AND
   feature.plot_number = feat_measurement.plot_number AND
   (feat_measurement.measurement_code = 'NSNP' OR feat_measurement.measurement_code IS NULL) AND
   feature.site_code = feat_attrib_type_3.site_code AND
   feature.plot_number = feat_attrib_type_3.plot_number AND
   (feat_attrib_type_3.attrib_type_code = 'OM' OR feat_attrib_type_3.attrib_type_code IS NULL) AND
   feature.site_code = feat_attrib_type_4.site_code AND
   feature.plot_number = feat_attrib_type_4.plot_number AND
   (feat_attrib_type_4.attrib_type_code = 'PMNT' OR feat_attrib_type_4.attrib_type_code IS NULL) AND
   feature.site_code = feat_attrib_type_5.site_code AND
   feature.plot_number = feat_attrib_type_5.plot_number AND
   (feat_attrib_type_5.attrib_type_code = 'WMNT' OR feat_attrib_type_5.attrib_type_code IS NULL)
ORDER BY
   central_site.site_code ASC

Best Answer

As Mihai said, use a LEFT JOIN on feat_attrib_type_4 and feat_attrib_type_5. Either old style (as you have) or ANSI joins style would work. For the former add (+) after every reference to a column in the where clause from those two aliases. For the latter, convert the statement to ANSI join syntax and add LEFT before the word JOIN for those two tables.

Related Solutions

Sql-server – Database design: Dividing multiple identical tables, good or bad

You should use one table for this, unless you need more stringent constraints.

Each area has the same number of rows and the same number of seats per row.

Let's say there are 5 rows in every area, and 6 seats in every row. You'd want to use something along these lines.

create table seats (
  area char(1) not null check (area in ('A', 'B', 'C', 'D')),
  row integer not null check ( row between 1 and 5 ),
  seat integer not null check ( seat between 1 and 6 ),
  primary key (area, row, seat)
);

To select a single seat, put three values in the WHERE clause.

select *
from seats
where area = 'A' and
       row =  1   and
      seat =  2;

To use a table like this to model seat reservations, populate it with every possible area, row, and seat. Then set a foreign key reference to it.

create table reservations (
  performance_time datetime not null,
  party_name varchar(40) not null,
  area char(1) not null,
  row integer not null,
  seat integer not null,
  primary key (performance_time, party_name, area, row, seat),
  foreign key (area, row, seat) references seats (area, row, seat)
);

You can see all the seating for a performance with this query.

select s.area, s.row, s.seat, r.performance_time, r.party_name
from seats s
left join reservations r
       on r.area = s.area and
          r.row  = s.row  and
          r.seat = s.seat and
          r.performance_time = '2013-04-30 08:00 pm'

And you can get all the available seats for a performance with something along these lines.

with seating as (
  select s.area, s.row, s.seat, r.performance_time, r.party_name
  from seats s
  left join reservations r
         on r.area = s.area and
            r.row  = s.row  and
            r.seat = s.seat and
            r.performance_time = '2013-04-30 08:00 pm'
)
select *
from seating
where performance_time is null

By default, SQL Server will create a clustered index for a primary key constraint. You'll want to give careful thought to the order of columns in your primary key constraints, and consider adding other indexes as well. (Especially since your outputs will often need to be ordered by area, row, and seat.)

Sql-server – Deadlock occurring on database, SQL Server 2008R2

Enabling snapshot isolation on a production server without testing the application first is rather a high risk strategy, but if you seem to have got away with it, congratulations.

The side effects of snapshot isolation tend to be to do with tempdb rather than the transaction log, see for instance http://msdn.microsoft.com/en-us/library/tcbchxcb%28v=vs.110%29.aspx.

The best thing to do is to look at the code that is accessing those two tables, and see if you can correct the problem there.

Best Answer

Related Solutions

Sql-server – Database design: Dividing multiple identical tables, good or bad

Sql-server – Deadlock occurring on database, SQL Server 2008R2

Related Question