RV: independent tables

RV: independent tables

am 04.05.2011 16:57:34 von Rocio Gomez Escribano

------=_NextPart_000_003B_01CC0A7C.5E66CE40
Content-Type: multipart/alternative;
boundary="----=_NextPart_001_003C_01CC0A7C.5E66F550"


------=_NextPart_001_003C_01CC0A7C.5E66F550
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

The databas estructure:

=20

mysql> describe user;

+------------------------+-------------+------+-----+------- --+----------=
---
---+

| Field | Type | Null | Key | Default | Extra
|

+------------------------+-------------+------+-----+------- --+----------=
---
---+

| userID | int(11) | NO | PRI | NULL |
auto_increment |

| userName | varchar(20) | YES | | NULL |
|

| userCodeDrivingLicense | varchar(20) | YES | | NULL |
|

+------------------------+-------------+------+-----+------- --+----------=
---
---+

3 rows in set (0.00 sec)

=20

mysql> describe client;

+--------------------------+-------------+------+-----+----- ----+--------=
---
-----+

| Field | Type | Null | Key | Default | Extra
|

+--------------------------+-------------+------+-----+----- ----+--------=
---
-----+

| clientID | int(11) | NO | PRI | NULL |
auto_increment |

| clientName | varchar(20) | YES | | NULL |
|

| clientCodeDrivingLicense | varchar(20) | YES | | NULL |
|

+--------------------------+-------------+------+-----+----- ----+--------=
---
-----+

3 rows in set (0.00 sec)

=20

Roc=EDo G=F3mez Escribano

=
r.gomez@ingenia-soluciones.com

=20

Descripci=F3n: cid:image002.jpg@01CB8CB6.ADEBA830

Pol=EDgono Campollano C/F, n=BA21T

02007 Albacete (Espa=F1a)

Tlf:967-504-513 Fax: 967-504-513

www.ingenia-soluciones.com

=20

De: Rocio Gomez Escribano [mailto:r.gomez@ingenia-soluciones.com]=20
Enviado el: martes, 03 de mayo de 2011 8:09
Para: mysql@lists.mysql.com
Asunto: independent tables

=20

Everyone has his/her own driving license, and I need to know what kind =
of
=93person=94 (client or user) is.

=20

=20

mysql> select userID, clientID from client, user where
(clientCodeDrivingLicense=3D 321321321 || userCodeDrivingLicense =3D =
321321321);

+--------+-------+

| userID | clientID |

+--------+-------+

| 1 | 2 |

| 2 | 2 |

| 3 | 2 |

| 4 | 2 |

| 5 | 2 |

+--------+-------+

5 rows in set (0.00 sec)

=20

But, what I want is something like that:

=20

+--------+-------+

| userID | clientID |

+--------+-------+

| Null | 2 |

+--------+-------+

=20

I tried something like this:

=20

select COUNT(DISTINCT u.userID), userID, clientID from client, user =
where
(clientCodeDrivingLicense =3D 321321321 || userCodeDrivingLicense =3D
321321321);

+--------------------------+--------+-------+

| COUNT(DISTINCT u.userID) | userID | clientID |

+--------------------------+--------+-------+

| 5 | 1 | 2 |

+--------------------------+--------+-------+

1 row in set (0.00 sec)

=20

But it wont be efficient enough in the future.

=20

I suppose my solution is an Join, but they have no intersection, so, I =
cant
imagine how do it

=20

Thank you!!

=20

Regards

=20

=20

=20

=20

=20

Roc=EDo G=F3mez Escribano

=
r.gomez@ingenia-soluciones.com

=20

Descripci=F3n: cid:image002.jpg@01CB8CB6.ADEBA830

Pol=EDgono Campollano C/F, n=BA21T

02007 Albacete (Espa=F1a)

Tlf:967-504-513 Fax: 967-504-513

www.ingenia-soluciones.com

=20


------=_NextPart_001_003C_01CC0A7C.5E66F550
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

charset=3Diso-8859-1">
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" =
xmlns:p=3D"urn:schemas-microsoft-com:office:powerpoint" =
xmlns:a=3D"urn:schemas-microsoft-com:office:access" =
xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" =
xmlns:s=3D"uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" =
xmlns:rs=3D"urn:schemas-microsoft-com:rowset" xmlns:z=3D"#RowsetSchema" =
xmlns:b=3D"urn:schemas-microsoft-com:office:publisher" =
xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadsheet" =
xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" =
xmlns:odc=3D"urn:schemas-microsoft-com:office:odc" =
xmlns:oa=3D"urn:schemas-microsoft-com:office:activation" =
xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" =
xmlns:rtc=3D"http://microsoft.com/officenet/conferencing" =
xmlns:D=3D"DAV:" xmlns:Repl=3D"http://schemas.microsoft.com/repl/" =
xmlns:mt=3D"http://schemas.microsoft.com/sharepoint/soap/mee tings/" =
xmlns:x2=3D"http://schemas.microsoft.com/office/excel/2003/x ml" =
xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" =
xmlns:ois=3D"http://schemas.microsoft.com/sharepoint/soap/oi s/" =
xmlns:dir=3D"http://schemas.microsoft.com/sharepoint/soap/di rectory/" =
xmlns:ds=3D"http://www.w3.org/2000/09/xmldsig#" =
xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint/dsp" =
xmlns:udc=3D"http://schemas.microsoft.com/data/udc" =
xmlns:xsd=3D"http://www.w3.org/2001/XMLSchema" =
xmlns:sub=3D"http://schemas.microsoft.com/sharepoint/soap/20 02/1/alerts/"=
xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#" =
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" =
xmlns:sps=3D"http://schemas.microsoft.com/sharepoint/soap/" =
xmlns:xsi=3D"http://www.w3.org/2001/XMLSchema-instance" =
xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/soap" =
xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " =
xmlns:udcp2p=3D"http://schemas.microsoft.com/data/udc/partto part" =
xmlns:wf=3D"http://schemas.microsoft.com/sharepoint/soap/wor kflow/" =
xmlns:dsss=3D"http://schemas.microsoft.com/office/2006/digsi g-setup" =
xmlns:dssi=3D"http://schemas.microsoft.com/office/2006/digsi g" =
xmlns:mdssi=3D"http://schemas.openxmlformats.org/package/200 6/digital-sig=
nature" =
xmlns:mver=3D"http://schemas.openxmlformats.org/markup-compa tibility/2006=
" xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns:mrels=3D"http://schemas.openxmlformats.org/package/200 6/relationshi=
ps" xmlns:spwp=3D"http://microsoft.com/sharepoint/webpartpages" =
xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/2006/types"=
=
xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/2006/messag=
es" =
xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ SlideLibrary/=
" =
xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor talServer/Pub=
lishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" =
xmlns:st=3D"" xmlns=3D"http://www.w3.org/TR/REC-html40"> name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)"> vlink=3Dpurple>

name=3D"_MailEndCompose">The databas =
estructure:

style=3D'color:#1F497D'> 

class=3DMsoNormal>mysql> describe =
user;

style=3D'color:#1F497D'>+------------------------+---------- ---+------+--=
---+---------+----------------+

class=3DMsoNormal>| =
Field                =A0 | =
Type      =A0 | Null | Key | Default | =
Extra        =A0 |

class=3DMsoNormal> style=3D'color:#1F497D'>+------------------------+---------- ---+------+--=
---+---------+----------------+

class=3DMsoNormal>| =
userID                 | =
int(11)     | NO   | PRI | NULL  =A0 | auto_increment =
|

style=3D'color:#1F497D'>| =
userName               | varchar(20) | =
YES=A0 |     | NULL  =A0 =
|              =A0 |

class=3DMsoNormal>| userCodeDrivingLicense =
| varchar(20) | YES=A0 |     | NULL  =A0 =
|              =A0 |

class=3DMsoNormal> style=3D'color:#1F497D'>+------------------------+---------- ---+------+--=
---+---------+----------------+

class=3DMsoNormal>3 rows in set (0.00 =
sec)

style=3D'color:#1F497D'> 

class=3DMsoNormal>mysql> describe =
client;

style=3D'color:#1F497D'>+--------------------------+-------- -----+------+=
-----+---------+----------------+

class=3DMsoNormal>| =
Field                  = A0 | =
Type      =A0 | Null | Key | Default | =
Extra        =A0 |

class=3DMsoNormal> style=3D'color:#1F497D'>+--------------------------+-------- -----+------+=
-----+---------+----------------+

class=3DMsoNormal>| =
clientID                 | =
int(11)     | NO   | PRI | NULL  =A0 | auto_increment =
|

style=3D'color:#1F497D'>| =
clientName               | varchar(20) | =
YES=A0 |     | NULL  =A0 =
|              =A0 |

class=3DMsoNormal>| =
clientCodeDrivingLicense | varchar(20) | YES=A0 |     | =
NULL  =A0 |              =A0 =
|

style=3D'color:#1F497D'>+--------------------------+-------- -----+------+=
-----+---------+----------------+

class=3DMsoNormal>3 rows in set (0.00 =
sec)

style=3D'color:#1F497D'> 

class=3DMsoNormal> style=3D'font-size:10.0pt;color:#0070C0'>Roc=EDo G=F3mez =
Escribano

href=3D"mailto:r.sanchez@ingenia-soluciones.com"> style=3D'font-size:10.0pt'>r.gomez@ingenia-soluciones.com lang=3DES-PE =
style=3D'font-size:10.0pt;color:#9D9D9D'>

class=3DMsoNormal> style=3D'color:blue'> 

class=3DMsoNormal> border=3D0 width=3D181 height=3D74 id=3D"_x0000_i1026" =
src=3D"cid:image001.jpg@01CC0A7C.5D7123A0" alt=3D"Descripci=F3n: =
cid:image002.jpg@01CB8CB6.ADEBA830"> style=3D'font-size:12.0pt;color:blue'>

class=3DMsoNormal> style=3D'font-size:10.0pt;color:#A6A6A6'>Pol=EDgono Campollano C/F, =
n=BA21T style=3D'font-size:10.0pt;color:blue'>

class=3DMsoNormal>02007 =
Albacete (Espa=F1a)

lang=3DEN-GB =
style=3D'font-size:10.0pt;color:#9D9D9D'>Tlf:967-504-513  Fax: =
967-504-513

href=3D"www.ingenia-soluciones.com" =
title=3D"blocked::www.ingenia-soluciones.com"> style=3D'font-size:10.0pt;color:#9D9D9D'>www.ingenia-solucio nes.com > style=3D'font-size:10.0pt;color:#A6A6A6'>

class=3DMsoNormal> style=3D'color:#1F497D'> 

style=3D'border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm =
0cm 0cm'>

style=3D'font-size:10.0pt;font-family:"Tahoma","sans-serif"' >De: b> =
Rocio Gomez Escribano [mailto:r.gomez@ingenia-soluciones.com] =

Enviado el: martes, 03 de mayo de 2011 8:09
Para: =
mysql@lists.mysql.com
Asunto: independent =
tables

class=3DMsoNormal> 

lang=3DEN-US>Everyone has his/her own driving license, and I need to =
know what kind of “person” (client or user) =
is.

lang=3DEN-US> 

lang=3DEN-US> 

lang=3DEN-US>mysql> select userID, clientID from client, user where =
(clientCodeDrivingLicense=3D 321321321 || userCodeDrivingLicense =3D =
321321321);

lang=3DEN-US>+--------+-------+

class=3DMsoNormal>| userID | clientID =
|

lang=3DEN-US>+--------+-------+

class=3DMsoNormal>|      1 =
|     2 |

class=3DMsoNormal>|      2 =
|     2 |

class=3DMsoNormal>|      3 =
|     2 |

class=3DMsoNormal>|      4 =
|     2 |

class=3DMsoNormal>|      5 =
|     2 |

class=3DMsoNormal> lang=3DEN-US>+--------+-------+

class=3DMsoNormal>5 rows in set (0.00 =
sec)

lang=3DEN-US> 

lang=3DEN-US>But, what I want is something like =
that:

lang=3DEN-US> 

lang=3DEN-US>     =
+--------+-------+

lang=3DEN-US>     | userID | clientID =
|

lang=3DEN-US>    =
 +--------+-------+

lang=3DEN-US>     |      =
Null |     2 |

class=3DMsoNormal>    =
 +--------+-------+

lang=3DEN-US> 

lang=3DEN-US>I tried something like this:

class=3DMsoNormal> 

class=3DMsoNormal>select COUNT(DISTINCT u.userID), =
userID, clientID from client, user  where (clientCodeDrivingLicense =
=3D 321321321 || userCodeDrivingLicense =3D =
321321321);

lang=3DEN-US>+--------------------------+--------+-------+ an>

| COUNT(DISTINCT =
u.userID) | userID | clientID |

class=3DMsoNormal> lang=3DEN-US>+--------------------------+--------+-------+ an>

lang=3DEN-US>|          =
;            =
  5 |      1 |     2 =
|

lang=3DEN-US>+--------------------------+--------+-------+ an>

1 row in set (0.00 =
sec)

lang=3DEN-US> 

lang=3DEN-US>But it wont be efficient enough in the =
future.

lang=3DEN-US> 

lang=3DEN-US>I suppose my solution is an Join, but they have no =
intersection, so, I cant imagine how do it

class=3DMsoNormal> 

class=3DMsoNormal>Thank you!!

class=3DMsoNormal> 

class=3DMsoNormal>Regards

class=3DMsoNormal> 

class=3DMsoNormal> 

class=3DMsoNormal> 

class=3DMsoNormal> 

class=3DMsoNormal> 

class=3DMsoNormal> style=3D'font-size:10.0pt;color:#0070C0'>Roc=EDo G=F3mez =
Escribano

lang=3DES-PE style=3D'font-size:10.0pt;color:#9D9D9D'> href=3D"mailto:r.sanchez@ingenia-soluciones.com"> lang=3DEN-US>r.gomez@ingenia-soluciones.com lang=3DEN-US =
style=3D'font-size:10.0pt;color:#9D9D9D'>

class=3DMsoNormal> style=3D'color:blue'> 

class=3DMsoNormal> border=3D0 width=3D181 height=3D74 id=3D"Imagen_x0020_1" =
src=3D"cid:image001.jpg@01CC08F1.3AC45E30" alt=3D"Descripci=F3n: =
cid:image002.jpg@01CB8CB6.ADEBA830"> style=3D'font-size:12.0pt;color:blue'>

class=3DMsoNormal> style=3D'font-size:10.0pt;color:#A6A6A6'>Pol=EDgono Campollano C/F, =
n=BA21T style=3D'font-size:10.0pt;color:blue'>

class=3DMsoNormal>02007 =
Albacete (Espa=F1a)

lang=3DEN-GB =
style=3D'font-size:10.0pt;color:#9D9D9D'>Tlf:967-504-513  Fax: =
967-504-513

style=3D'font-size:10.0pt;color:#A6A6A6'> href=3D"www.ingenia-soluciones.com" =
title=3D"blocked::www.ingenia-soluciones.com"> style=3D'color:#9D9D9D'>www.ingenia-soluciones.com lang=3DEN-GB =
style=3D'font-size:10.0pt;color:#A6A6A6'>

class=3DMsoNormal> lang=3DEN-US> 


------=_NextPart_001_003C_01CC0A7C.5E66F550--

------=_NextPart_000_003B_01CC0A7C.5E66CE40--

Re: RV: independent tables

am 04.05.2011 22:43:05 von (Halász Sándor) hsv

>>>> 2011/05/04 16:57 +0200, Rocio Gomez Escribano >>>>
I suppose my solution is an Join, but they have no intersection
<<<<<<<<
Really?

Your examples are very much like a simple join, a special case of

.... client OUTER JOIN user ON clientCodeDrivingLicense = userCodeDrivingLicense

What is wrong with that? (although actually MySQL does not do full outer joining. It is needful to get that through a union of left join and right join, care taken that the inner join in only one of them appear.)

Actually, your tables "client" and "user" look like the same table with field names changed, no other difference. Field names have nothing to do with intersection.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: RV: independent tables

am 06.05.2011 10:00:29 von Rocio Gomez Escribano

Tables "client" an "user" are quite similar, but they don't have any
intersection, I mean, if somebody is a client, he or she cant be a user. =
So,
I have his or her driving license and I need to know what kind of person =
is.

Im trying some join left, right, but I'm unable to get it!!

Roc=EDo G=F3mez Escribano
r.gomez@ingenia-soluciones.com


Pol=EDgono Campollano C/F, n=BA21T
02007 Albacete (Espa=F1a)
Tlf:967-504-513=A0 Fax: 967-504-513
www.ingenia-soluciones.com

-----Mensaje original-----
De: Hal=E1sz S=E1ndor [mailto:hsv@tbbs.net]=20
Enviado el: mi=E9rcoles, 04 de mayo de 2011 22:43
Para: Rocio Gomez Escribano
CC: mysql@lists.mysql.com
Asunto: Re: RV: independent tables

>>>> 2011/05/04 16:57 +0200, Rocio Gomez Escribano >>>>
I suppose my solution is an Join, but they have no intersection
<<<<<<<<
Really?

Your examples are very much like a simple join, a special case of

.... client OUTER JOIN user ON clientCodeDrivingLicense =3D
userCodeDrivingLicense

What is wrong with that? (although actually MySQL does not do full outer
joining. It is needful to get that through a union of left join and =
right
join, care taken that the inner join in only one of them appear.)

Actually, your tables "client" and "user" look like the same table with
field names changed, no other difference. Field names have nothing to do
with intersection.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: RV: independent tables

am 06.05.2011 10:34:47 von nwood

On Fri, 2011-05-06 at 09:00 +0100, Rocio Gomez Escribano wrote:
> Tables "client" an "user" are quite similar, but they don't have any
> intersection, I mean, if somebody is a client, he or she cant be a user. So,
> I have his or her driving license and I need to know what kind of person is.
>
> Im trying some join left, right, but I'm unable to get it!!
>

OK, a couple of questions:

Are you absolutely sure the two sets of people are mutually exclusive?
Why use two separate "user" and "client" tables rather than one "person"
table with a typeId which foreign keys on to a personType table? Is the
output of two columns named PersonType,PersonId acceptable for this
query?

Assuming your design is correct as it stands or fixed in its current
state you can achieve what you want with a join.

select null as userID, clientID
from client
where clientCodeDrivingLicense = 321321321
UNION
select userId, null as clientID
from user
where userCodeDrivingLicense = 321321321

With regard to the performance of this system over time I'd suggest you
want a unique index on the DrivingLicense column/columns.

Hope that helps,

Nigel




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: RV: independent tables

am 06.05.2011 11:55:02 von Phil

--000e0cd4bbb005be0604a2987db7
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Why not just use a union ?

select userID,NULL as clientID from user where userCodeDrivingLicense =3D
'321321321' union select NULL as userID,clientID from client where
clientCodeDrivingLicense =3D '321321321';



2011/5/6 Rocio Gomez Escribano

> Tables "client" an "user" are quite similar, but they don't have any
> intersection, I mean, if somebody is a client, he or she cant be a user.
> So,
> I have his or her driving license and I need to know what kind of person
> is.
>
> Im trying some join left, right, but I'm unable to get it!!
>
> Roc=EDo G=F3mez Escribano
> r.gomez@ingenia-soluciones.com
>
>
> Pol=EDgono Campollano C/F, n=BA21T
> 02007 Albacete (Espa=F1a)
> Tlf:967-504-513 Fax: 967-504-513
> www.ingenia-soluciones.com
>
> -----Mensaje original-----
> De: Hal=E1sz S=E1ndor [mailto:hsv@tbbs.net]
> Enviado el: mi=E9rcoles, 04 de mayo de 2011 22:43
> Para: Rocio Gomez Escribano
> CC: mysql@lists.mysql.com
> Asunto: Re: RV: independent tables
>
> >>>> 2011/05/04 16:57 +0200, Rocio Gomez Escribano >>>>
> I suppose my solution is an Join, but they have no intersection
> <<<<<<<<
> Really?
>
> Your examples are very much like a simple join, a special case of
>
> ... client OUTER JOIN user ON clientCodeDrivingLicense =3D
> userCodeDrivingLicense
>
> What is wrong with that? (although actually MySQL does not do full outer
> joining. It is needful to get that through a union of left join and right
> join, care taken that the inner join in only one of them appear.)
>
> Actually, your tables "client" and "user" look like the same table with
> field names changed, no other difference. Field names have nothing to do
> with intersection.
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dpchapman@nc.rr.co=
m
>
>


--=20
Distributed Computing stats
http://stats.free-dc.org

--000e0cd4bbb005be0604a2987db7--

RE: RV: independent tables

am 07.05.2011 00:26:20 von (Halász Sándor) hsv

>>>> 2011/05/06 10:00 +0200, Rocio Gomez Escribano >>>>
Tables "client" an "user" are quite similar, but they don't have any
intersection, I mean, if somebody is a client, he or she cant be a user. So,
I have his or her driving license and I need to know what kind of person is.
<<<<<<<<
Two others already have said it, that a union seems the real solution, and here is another variation:

SELECT 'user' AS kind, userID AS ID, userName AS Name, userCodeDrivingLicense AS DrivingLicense FROM user
UNION
SELECT 'client' AS kind, client.* FROM client

It is also good to make a view out of it (or the other schemes, if they better suit). Then from the view, call it "uc", one does

SELECT * FROM uc WHERE DrivingLicense = 321321321

and, whichever the scheme, sees by the outcome which table matched.

But if, as already said, the tables "client" and "user" are made one, with a distinguishing field, there is one distinct advantage, that if "DrivingLicense" is made unique the notion that noöne is both user and client is enforced. With two separate tables there is nothing to keep someone from entering the same "DrivingLicense" in both tables.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org