Dumb join - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Dumb join

Author

Topic

dr fonz
Starting Member

6 Posts

Posted - 2002-02-26 : 06:26:36

I have two tables, containing information on sites and housetypes. a site has n housetypes, and any housetype can belong to n sites, so i have the tables as follows, with a join table:

table Site
(ID, Site name etc)

table Housetype
(ID, Housetype name, etc)

join table SiteHousetype
(SiteID, HousetypeID)

This stuff is being displayed on a web page which will be showing either of the following:

a) Summary information on a particular site;
b) Summary information on a particular site AND a housetype on that site.

So I either have a SiteID or a SiteID AND a HousetypeID.

What I want is a single query that will deal with both situations, so that if I have a SiteID it will return the site info (and nulls or whatever for the housetype info) and if I have a SiteID AND a HousetypeID it will return the info for the site and the housetype. Either way, the query should only return a single row (or no row if the SiteID doesn't match, I guess).

I've tried various joins, but they don't work (which wasn't surprising cos the joins I was trying aren't supposed to work like that).

Any solutions, or am I just being dumb?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-26 : 06:47:08

Take a look at the ROLLUP operator (it's in Books Online). It generates sub-group summary rows, so you can get HouseType summaries and then ROLLUP the entire SiteID:

CREATE PROCEDURE SiteSummary @siteID int AS
SELECT SiteID, HouseTypeID, Count(*) Total
FROM SiteHouseType
WHERE SiteID=@siteID
GROUP BY SiteID, HouseTypeID WITH ROLLUP

You'll get an extra row with NULL for HouseTypeID that will summarize all of the previous rows.

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-04 : 07:42:36

If you would prefer "Total" to NULL when using Rollup then use...


SELECT 
case grouping(SiteID) when 0 then SiteID else 'Total' end as SiteID,
case grouping(HouseTypeID) when 0 then HouseTypeID else 'Total' end as HouseTypeID,
Count(*) as Total
FROM SiteHouseType
WHERE SiteID=@siteID
GROUP BY SiteID, HouseTypeID WITH ROLLUP

============
The Dabbler!

Edited by - davidpardoe on 03/04/2002 07:43:07

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-03-25 : 12:26:11

>> I have two tables, containing information on sites and housetypes. <<

Please post DDL instead of prose in the future.

>> a site has n housetypes, and any housetype can belong to n sites, so i have the tables as follows, with a join table: <<

It is called a relationship table, not a JOIN table. If you had posted DDL, would it look like this? The columns named "HousetypeID" made absolutely no sense in a relational data model; a "type" is an attribute; an attribute has a value -- it NEVER has an id.

CREATE TABLE Sites
(site_id INTEGER NOT NULL PRIMARY KEY,
site_name VARCHAR(30) NOT NULL,
...);

CREATE TABLE Housetypes
(house_code CHAR(5) NOT NULL PRIMARY KEY,
house_code_description VARCHAR(100) NOT NULL,
...);

CREATE TABLE Sites_Houses
(site_id INTEGER NOT NULL
REFERENCES Sites (site_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
house_code CHAR(5) NOT NULL
REFERENCES Housetypes (house_code)
ON UPDATE CASCADE,
PRIMARY KEY (site_id, house_code));

>> a) Summary information on a particular site;
b) Summary information on a particular site AND a housetype on that site. <<

Could you make the specification a little clearer than "Summary Information"?

>> So I either have a SiteID or a SiteID AND a HousetypeID.<<

SELECT @my_site_id, COALESCE (@my_house_code, '*All*'),
<<insert magic summary functions here>>
FROM Sites_Houses AS SH1,
Sites AS S1, Housetypes AS H1
WHERE SH1.site_id = @my_site_id
AND S1.site_id = @my_site_id
AND SH1.house_code = COALESCE (@my_house_code,house_code)
GROUP BY @my_site_id, @my_house_code;

>> Any solutions, or am I just being dumb? <<

No, just painfully vague ...

--CELKO--
Joe Celko, SQL Guru

leeholden
Starting Member

34 Posts

Posted - 2002-03-28 : 11:49:20

David,
What happened to keeping NULLs as NULLs?

jcelko,
My house type is a "Condo" it has an ID of 3, what is yours?

jongregg
Starting Member

31 Posts

Posted - 2002-03-28 : 11:55:31

quote:

CREATE TABLE Sites_Houses
(site_id INTEGER NOT NULL
REFERENCES Sites (site_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
house_code CHAR(5) NOT NULL
REFERENCES Housetypes (house_code)
ON UPDATE CASCADE,
PRIMARY KEY (site_id, house_code));

Hmm, what about a solution for those of us still using SQL Server 7?

dr fonz
Starting Member

6 Posts

Posted - 2002-07-05 : 10:27:08

Ta for the answer jcelko, I enjoy the occasional jibe (and "vague" is my middle name)... anyway, though I am aware that a "type" has an attribute, etc, etc, life isn't always that simple - the client has sites, and a site contains a number of houses, each of a particular housetype, and each housetype has an ID! Just like Lee, 'cept Lee's "Condo" is actually a housetypecategory, because each housetype belongs to one of those too!

I bought a house in Bristol Fields, which was of the "chuntsworth" house type (3 bedrooms, one en-suite, w' driveway et al), a detached home... But then I am a sad middle-englander after all!

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-07-09 : 05:35:49

quote:

table Site
(ID, Site name etc)

table Housetype
(ID, Housetype name, etc)

join table SiteHousetype
(SiteID, HousetypeID)

select s.*, h.*
from site s LEFT OUTER JOIN Housetype h on
s.siteid = h.housetypeid

This will always show site if it exists and will include housetype if it exists.
You cannot do a where on housetype fields unless you include a (or is null) clause at the end.

Look at coalesce to get rid of the NULLs coalesce(h.type,'') etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources