[Wolves] Happy Christmas ...and help me with this mysql query

[Chris Ellis]

Hi Wayne

On 28 December 2012 14:47, Wayne Morris <waynelists at machx.co.uk> wrote:
> On 27/12/2012 18:19, Andy Smith wrote:
> <lots of useful stuff>
>
> Hi,
>  I should clarify that Part1 on Item1 is not the same as Item1 on Part2,
> just the name is the same!
>  So having a separate table for the Parts is a bit pointless.
> Lets try again with houses, I want a list of unique houses at the end.
>
> Each house has a distinct street address, possible same postcode (for two
> houses next door), same town.
> Each house is broken up into Rooms - room1 -> room 14 (big apartment
> blocks).
>
> So all I did was set up one table for all 750 unique rooms, having ID,
> street address (address2) , Room No (address1) postcode as fields (and then
> sub tables for tenants, histories etc).

While you think this is simple, you have run into the reason why it is
bad practise.  When designing a database, your goal should be to *NOT*
duplicate data.  Hence why this should be broken into two tables,
rooms relate to a property.  You are also running into problems around
natural keys and beloved auto-incrementing IDs.

You should break the data into two tables:

(In PostgreSQL SQL)

CREATE TABLE property (
    id serial PRIMARY KEY,
    address TEXT,
    CONSTRAINT property_pk PRIMARY KEY (id)
);

CREATE TABLE room (
    property integer REFERENCES property (id),
    room TEXT,
    CONSTRAINT room_pk PRIMARY KEY (property, room)
)

As an example of why you should do this, what happends then you
realise the street address is incorrect and the record need to be
updated?  You don't actually have a unique ID for a property, this
seems shortsighted and foolish.

>
> So, trying to get a list of each individual house (about 150) rather than
> each room
>
> SELECT DISTINCT (address2 ) FROM property ORDER BY address2 LIMIT 0 , 300
> seems to produce a list of individual houses but
>
> I can't populate the report as if i change it to:
>
> SELECT DISTINCT (address2 ) FROM property as ID, address1, address2, rent
> ORDER BY address2 LIMIT 0 , 300
> it goes balmy with duplicates

I really hope MySQL doesn't actually execute this query, its utter bollox.

>
> select * from property where address2 like '%1%'  would be really simple
> query if I could get rid of the duplicates caused by rooms over 10

To actually query your table structure as is, either omit the ID field
when using SELECT DISTINCT, eg:

SELECT DISTINCT address_1, address_2 FROM properties WHERE address_1
LIKE '%blah%' ORDER BY address_1, address_2;

If your insistent on returning your pointless autoinc ID too, you need
to get more complex:

SELECT q.*, (SELECT p.id FROM properties p WHERE p.address_1 =
q.address_1 AND p.address_2 = q.address_2 LIMIT 1) AS id FROM (
	SELECT DISTINCT address_1, address_2 FROM properties ORDER BY
address_1, address_2
) q
WHERE address_1 LIKE '%1%'
ORDER BY address_1, address_2;

I'm a PostgreSQL DBA, so prefer PostgreSQL, which has a rather handy
form of DISTINCT, which as far as I'm aware MySQL doesn't support, in
PostgreSQL I can do:

CREATE TABLE properties (
	id SERIAL,
	address_1 TEXT,
	address_2 TEXT,
	room TEXT,
	CONSTRAINT properies_pk PRIMARY KEY (id)
);

INSERT INTO properties (address_1, address_2, room) VALUES ('Street1',
'Street1.2', 'Room1');
INSERT INTO properties (address_1, address_2, room) VALUES ('Street1',
'Street1.2', 'Room2');
INSERT INTO properties (address_1, address_2, room) VALUES ('Street1',
'Street1.2', 'Room3');

INSERT INTO properties (address_1, address_2, room) VALUES ('Street2',
'Street2.2', 'Room1');
INSERT INTO properties (address_1, address_2, room) VALUES ('Street2',
'Street2.2', 'Room2');
INSERT INTO properties (address_1, address_2, room) VALUES ('Street2',
'Street2.2', 'Room3');

INSERT INTO properties (address_1, address_2, room) VALUES ('Street3',
'Street3.2', 'Room1');
INSERT INTO properties (address_1, address_2, room) VALUES ('Street3',
'Street3.2', 'Room2');
INSERT INTO properties (address_1, address_2, room) VALUES ('Street3',
'Street3.2', 'Room3');

INSERT INTO properties (address_1, address_2, room) VALUES
('Street10', 'Street10.2', 'Room1');
INSERT INTO properties (address_1, address_2, room) VALUES
('Street10', 'Street10.2', 'Room2');
INSERT INTO properties (address_1, address_2, room) VALUES
('Street10', 'Street10.2', 'Room3');


SELECT DISTINCT ON (address_1, address_2) id, address_1, address_2
FROM properties
WHERE address_1 like '%1%'
ORDER BY address_1, address_2;

In summary, my advice would be to revise your table structure so it is
sane.  Databases are very good at joining data between tables, I
suggest you look at:
http://en.wikipedia.org/wiki/Database_normalization

Regards,
Chris E.