a SQL Statement/Union Help

a SQL Statement/Union Help

Post by RLee » Wed, 05 Feb 2003 08:10:19



Hello..

Here's the tables.

Create Table Item
(
    ItemID int identity(1,1) Primary Key,
    ISBN nvarchar(10),
    TitleText nvarchar(50)
)
go

Create Table Item2
(
    ItemID int identity(1,1) Primary Key,
    ISBN nvarchar(10),
    TitleText nvarchar(50)
)
go

Insert Item (ISBN, TitleText) values ('0345443829','Garfield at Large')
Insert Item (ISBN, TitleText) values ('0345441095','Garfield Beefs Up')
Insert Item (ISBN, TitleText) values ('0671024109','The Fall of Terok Nor')

Insert Item2 (ISBN, TitleText) values ('0345441095','Garfield Beefs Up')

Note, the ItemID for "Garfield Beefs Up' is different in both cases (1 for
Item, and 2 for Item2).

When i use this query, i get this.
SELECT DISTINCT 'Item' AS 'TableSource', ItemID, ISBN, TitleText
FROM ITEM
WHERE TitleText LIKE 'garfield%'
UNION
SELECT DISTINCT 'Item2' AS 'TableSource', ItemID, ISBN, TitleText
FROM ITEM2
WHERE TitleText LIKE 'garfield%'
ORDER BY TitleText

And the result.
TableSource ItemID      ISBN       TitleText
----------- ----------- ---------- -----------------------------------------
---------
Item        1           0345443829 Garfield at Large
Item2       1           0345441095 Garfield Beefs Up
Item        2           0345441095 Garfield Beefs Up

I would like to get the following result:

TableSource ItemID      ISBN       TitleText
----------- ----------- ---------- -----------------------------------------
---------
Item        1           0345443829 Garfield at Large
Item2       1           0345441095 Garfield Beefs Up

Basically, all Items from both Item and Item2 (which matches the search
criteria of "titletext like 'garfield%'"), and I don't want to see the title
from "Item", but only from "Item2".  The only similarity is the ISBN itself.
The ItemIDs are going to be different in both tables.

I tried to add
AND ISBN NOT IN (SELECT DISTINCT ISBN FROM Item2)
in both where clauses, no go...

Am i at least close to this?

Thanks to anyone who can help!

...Robin

 
 
 

a SQL Statement/Union Help

Post by oj » Wed, 05 Feb 2003 08:22:30


Robin,

select 'item' as source,*
from item i1
where TitleText LIKE 'garfield%'
and not exists(select *
from item2 i2
where i2.isbn=i1.isbn and i2.titletext=i1.titletext)
union
select 'item2',*
from item2
where TitleText LIKE 'garfield%'

--
-oj
RAC v2.1 & QALite are now RELEASED!!!
http://www.rac4sql.net


Quote:> Hello..

> Here's the tables.

> Create Table Item
> (
>     ItemID int identity(1,1) Primary Key,
>     ISBN nvarchar(10),
>     TitleText nvarchar(50)
> )
> go

> Create Table Item2
> (
>     ItemID int identity(1,1) Primary Key,
>     ISBN nvarchar(10),
>     TitleText nvarchar(50)
> )
> go

> Insert Item (ISBN, TitleText) values ('0345443829','Garfield at Large')
> Insert Item (ISBN, TitleText) values ('0345441095','Garfield Beefs Up')
> Insert Item (ISBN, TitleText) values ('0671024109','The Fall of Terok
Nor')

> Insert Item2 (ISBN, TitleText) values ('0345441095','Garfield Beefs Up')

> Note, the ItemID for "Garfield Beefs Up' is different in both cases (1 for
> Item, and 2 for Item2).

> When i use this query, i get this.
> SELECT DISTINCT 'Item' AS 'TableSource', ItemID, ISBN, TitleText
> FROM ITEM
> WHERE TitleText LIKE 'garfield%'
> UNION
> SELECT DISTINCT 'Item2' AS 'TableSource', ItemID, ISBN, TitleText
> FROM ITEM2
> WHERE TitleText LIKE 'garfield%'
> ORDER BY TitleText

> And the result.
> TableSource ItemID      ISBN       TitleText
> ----------- ----------- ---------- ---------------------------------------
--
> ---------
> Item        1           0345443829 Garfield at Large
> Item2       1           0345441095 Garfield Beefs Up
> Item        2           0345441095 Garfield Beefs Up

> I would like to get the following result:

> TableSource ItemID      ISBN       TitleText
> ----------- ----------- ---------- ---------------------------------------
--
> ---------
> Item        1           0345443829 Garfield at Large
> Item2       1           0345441095 Garfield Beefs Up

> Basically, all Items from both Item and Item2 (which matches the search
> criteria of "titletext like 'garfield%'"), and I don't want to see the
title
> from "Item", but only from "Item2".  The only similarity is the ISBN
itself.
> The ItemIDs are going to be different in both tables.

> I tried to add
> AND ISBN NOT IN (SELECT DISTINCT ISBN FROM Item2)
> in both where clauses, no go...

> Am i at least close to this?

> Thanks to anyone who can help!

> ...Robin


 
 
 

a SQL Statement/Union Help

Post by Joe Celk » Wed, 05 Feb 2003 09:31:47


You have a few problems with your design.  The ISBN is a unique
identifier, so that silly IDENTITY column is a dangerous redunancy. The
ISBN is *always* CHAR(10) and made up of digits and possible 'X' at the
end; it is not an NVARCHAR(n) datatype.  Perhaps you allow foreign
language in the titles, so title looks okay.

Now, the next question; why do you have two tables with the same
structure?  A table is a set of things of the same kind and all the same
kind of things belong to one and only one set.  This is called the
principle of orthogonal design.

It looks like there should not be an Item2 table, but instead one table
with a status code or something that tells us what the difference in
those two copies of Garfield were:

CREATE TABLE Books
(isbn CHAR(10) NOT NULL
     CHECK(isbn LIKE
'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9X]'),
 title NVARCHAR(50) NOT NULL,
 status INTEGER NOT NULL DEFAULT 1
       CHECK(status BEWTWEN 1 AND ??),
 PRIMARY KEY (isbn, status));

Now your problem is easy.  Otherwise, you will be doing UNIONs and all
kinds of complex queries that will kill your performance, make your code
a * to maintain, etc.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.veryComputer.com/ ***
Don't just participate in USENET...get rewarded for it!

 
 
 

a SQL Statement/Union Help

Post by BP Margoli » Wed, 05 Feb 2003 12:34:52


Joe,

Did it ever occur to you, before posting one of your usual rants, to at
least acknowledge that Robin was considerate enough and professional enough
to post DDL and sample data?

BPM


Quote:> You have a few problems with your design.  The ISBN is a unique
> identifier, so that silly IDENTITY column is a dangerous redunancy. The
> ISBN is *always* CHAR(10) and made up of digits and possible 'X' at the
> end; it is not an NVARCHAR(n) datatype.  Perhaps you allow foreign
> language in the titles, so title looks okay.

> Now, the next question; why do you have two tables with the same
> structure?  A table is a set of things of the same kind and all the same
> kind of things belong to one and only one set.  This is called the
> principle of orthogonal design.

> It looks like there should not be an Item2 table, but instead one table
> with a status code or something that tells us what the difference in
> those two copies of Garfield were:

> CREATE TABLE Books
> (isbn CHAR(10) NOT NULL
>      CHECK(isbn LIKE
> '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9X]'),
>  title NVARCHAR(50) NOT NULL,
>  status INTEGER NOT NULL DEFAULT 1
>        CHECK(status BEWTWEN 1 AND ??),
>  PRIMARY KEY (isbn, status));

> Now your problem is easy.  Otherwise, you will be doing UNIONs and all
> kinds of complex queries that will kill your performance, make your code
> a * to maintain, etc.

> --CELKO--
>  ===========================
>  Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.

> *** Sent via Developersdex http://www.veryComputer.com/ ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

a SQL Statement/Union Help

Post by RLee » Wed, 05 Feb 2003 23:38:27


Well, considering the fact that...

1. ISBN is NOT always unique... I work for a book wholesale company and
there is a 1% chance it isn't... The isbn can be incorrect, so in the
internal system here, we have to use a linkto type thing to link to the new
ISBN even though there are orders for the OLD isbn.
Because this part is for the web site, we did away with linking in
SQL/Access so that we could change the ISBN instead of adding a new record.
2. I'm not asking about how shitty my design is... I did this for a reason,
and if i didn't have a reason for this, I would NOT have done it.
    For those that are curious on the SECOND table of Item2, it's a separate
table for something else which i've been working on in the past month and a
half (i had asked one question previously in this newsgroup and the
responses were very useful, and worked for what i needed).  This table is
completely separate from the first table.
3. As BPM had mentioned, at least i was professional enough to research and
post data so that you all had something to work from.  These aren't the
exact tables, but a very VERY cut down version so that it is easy to work
with.


Quote:> You have a few problems with your design.  The ISBN is a unique
> identifier, so that silly IDENTITY column is a dangerous redunancy. The
> ISBN is *always* CHAR(10) and made up of digits and possible 'X' at the
> end; it is not an NVARCHAR(n) datatype.  Perhaps you allow foreign
> language in the titles, so title looks okay.

> Now, the next question; why do you have two tables with the same
> structure?  A table is a set of things of the same kind and all the same
> kind of things belong to one and only one set.  This is called the
> principle of orthogonal design.

> It looks like there should not be an Item2 table, but instead one table
> with a status code or something that tells us what the difference in
> those two copies of Garfield were:

> CREATE TABLE Books
> (isbn CHAR(10) NOT NULL
>      CHECK(isbn LIKE
> '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9X]'),
>  title NVARCHAR(50) NOT NULL,
>  status INTEGER NOT NULL DEFAULT 1
>        CHECK(status BEWTWEN 1 AND ??),
>  PRIMARY KEY (isbn, status));

> Now your problem is easy.  Otherwise, you will be doing UNIONs and all
> kinds of complex queries that will kill your performance, make your code
> a * to maintain, etc.

> --CELKO--
>  ===========================
>  Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.

> *** Sent via Developersdex http://www.veryComputer.com/ ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

a SQL Statement/Union Help

Post by RLee » Wed, 05 Feb 2003 23:44:28


Hi Oj,

   Thank you for helping me with this.  I got what i needed from your code,
and it works perfectly!

Again, thanks!

...Robin


> Robin,

> select 'item' as source,*
> from item i1
> where TitleText LIKE 'garfield%'
> and not exists(select *
> from item2 i2
> where i2.isbn=i1.isbn and i2.titletext=i1.titletext)
> union
> select 'item2',*
> from item2
> where TitleText LIKE 'garfield%'

> --
> -oj
> RAC v2.1 & QALite are now RELEASED!!!
> http://www.rac4sql.net



> > Hello..

> > Here's the tables.

> > Create Table Item
> > (
> >     ItemID int identity(1,1) Primary Key,
> >     ISBN nvarchar(10),
> >     TitleText nvarchar(50)
> > )
> > go

> > Create Table Item2
> > (
> >     ItemID int identity(1,1) Primary Key,
> >     ISBN nvarchar(10),
> >     TitleText nvarchar(50)
> > )
> > go

> > Insert Item (ISBN, TitleText) values ('0345443829','Garfield at Large')
> > Insert Item (ISBN, TitleText) values ('0345441095','Garfield Beefs Up')
> > Insert Item (ISBN, TitleText) values ('0671024109','The Fall of Terok
> Nor')

> > Insert Item2 (ISBN, TitleText) values ('0345441095','Garfield Beefs Up')

> > Note, the ItemID for "Garfield Beefs Up' is different in both cases (1
for
> > Item, and 2 for Item2).

> > When i use this query, i get this.
> > SELECT DISTINCT 'Item' AS 'TableSource', ItemID, ISBN, TitleText
> > FROM ITEM
> > WHERE TitleText LIKE 'garfield%'
> > UNION
> > SELECT DISTINCT 'Item2' AS 'TableSource', ItemID, ISBN, TitleText
> > FROM ITEM2
> > WHERE TitleText LIKE 'garfield%'
> > ORDER BY TitleText

> > And the result.
> > TableSource ItemID      ISBN       TitleText

> ----------- ----------- ---------- ---------------------------------------
> --
> > ---------
> > Item        1           0345443829 Garfield at Large
> > Item2       1           0345441095 Garfield Beefs Up
> > Item        2           0345441095 Garfield Beefs Up

> > I would like to get the following result:

> > TableSource ItemID      ISBN       TitleText

> ----------- ----------- ---------- ---------------------------------------
> --
> > ---------
> > Item        1           0345443829 Garfield at Large
> > Item2       1           0345441095 Garfield Beefs Up

> > Basically, all Items from both Item and Item2 (which matches the search
> > criteria of "titletext like 'garfield%'"), and I don't want to see the
> title
> > from "Item", but only from "Item2".  The only similarity is the ISBN
> itself.
> > The ItemIDs are going to be different in both tables.

> > I tried to add
> > AND ISBN NOT IN (SELECT DISTINCT ISBN FROM Item2)
> > in both where clauses, no go...

> > Am i at least close to this?

> > Thanks to anyone who can help!

> > ...Robin

 
 
 

a SQL Statement/Union Help

Post by oj » Thu, 06 Feb 2003 01:53:40


You're welcome.

G'luck.

--
-oj
RAC v2.1 & QALite are now RELEASED!!!
http://www.rac4sql.net


Quote:> Hi Oj,

>    Thank you for helping me with this.  I got what i needed from your
code,
> and it works perfectly!

> Again, thanks!

> ...Robin

 
 
 

a SQL Statement/Union Help

Post by Joe Celk » Sat, 08 Feb 2003 04:52:42


Quote:>> 1. ISBN is NOT always unique... I work for a book wholesale company

and there is a 1% chance it isn't... <<

I used to own three bookstores; I remember the duplicates and the
procedure for resolutions.  I thought that Browker had fixed that
problem about five years ago.

Quote:>> we have to use a linkto type thing to link to the new

ISBN even though there are orders for the OLD isbn. <<

We used ON UPDATE CASCADE to change the orders instead.  That way the
customer does not get confused when they try to locate titles.  

Quote:>> 2. I'm not asking about how shitty my design is... I did this for a

reason, and if i didn't have a reason for this, I would NOT have done
it. <<

Well, I should have guessed, since nobody ever has a bad schema design
<g>.   Enlighten me -- why NVARCHAR(n) for an ISBN?  All I can see this
design doing is wasting storage and allowing someone to key in garbage
in a foreign language.  What did I miss?  

Quote:>> For those that are curious on the SECOND table of Item2, it's a

separate table for something else ... <<

I should have guessed from the distinctive table name, which in no way
implies that it is related to Item?  Why not call it something like
"MyWishList" or "PersonalLibrary" instead?  

Quote:>> 3. As BPM had mentioned, at least i was professional enough to

research and post data so that you all had something to work from. <<

And I did work from it!  I am am glad that you did not just "push the
button" that generates the schema with a zillion square brackets, bad
spacing, and a dozen ALTER statements.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!