Selecting data from multiple tables

am 15.11.2009 13:37:45 von KirAsh4

RE: Selecting data from multiple tables

am 16.11.2009 04:50:14 von Gavin Towey

Re: Selecting data from multiple tables

am 18.11.2009 20:50:23 von Shawn Green

Ashley M. Kirchner wrote:
> Hi folks,
>
> I'm trying to, possibly do the impossible here. I have to select data from
> 4 different tables to come up with the right information and I'm having one
> heck of time trying to figure it out. This is going to be a long email ...
>
> Table_1:
> +-----------+--------------------------+------+-----+------- --+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------+--------------------------+------+-----+------- --+-------+
> | photo_uid | int(7) unsigned zerofill | NO | PRI | NULL | |
> | username | varchar(100) | NO | | NULL | |
> | votes | int(5) | YES | | 0 | |
> +-----------+--------------------------+------+-----+------- --+-------+
>
> Table_2:
> +-----------+--------------------------+------+-----+------- --+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------+--------------------------+------+-----+------- --+-------+
> | photo_uid | int(7) unsigned zerofill | NO | UNI | NULL | |
> | username | varchar(100) | NO | PRI | NULL | |
> | vote | int(2) | NO | | 0 | |
> | voted_on | datetime | NO | | NULL | |
> +-----------+--------------------------+------+-----+------- --+-------+
>
> Table_3:
> +------------+---------------------+------+-----+---------+- ---------------+
> | Field | Type | Null | Key | Default | Extra |
> +------------+---------------------+------+-----+---------+- ---------------+
> | username | varchar(100) | NO | UNI | NULL | |
> | info | varchar(100) | NO | | NULL | |
> +------------+---------------------+------+-----+---------+- ---------------+
>
> Table_4:
> +-----------+--------------------------+------+-----+------- --+----------------+
> | Field | Type | Null | Key | Default |
> Extra |
> +-----------+--------------------------+------+-----+------- --+----------------+
> | photo_uid | int(7) unsigned zerofill | NO | PRI | NULL |
> auto_increment |
> | username | varchar(100) | NO | | NULL
> | |
> | photo | varchar(100) | NO | | NULL
> | |
> +-----------+--------------------------+------+-----+------- --+----------------+
>
> Data used for query:
> username=foo
>
>
> The goal here is several.
> 1. query Table 3 for info where username=foo (always 1 record)
>
> 2. query Table 3 for username where info = (result of Q1 above) EXCLUDING
> username=foo
> (results in 0 to many records)
>
> 3. query Table 1 for photo_uid where username= (all records in query from
> Q2 above)
>
> 4. query Table 4 for photo_uid from Q2 above and EXCLUDING username=foo
>
>
> Now, I started fiddling with LEFT JOIN and came up with this:
>
> select * from Table_1 left join Table_2 using (photo_uid) where
> Table_1.username != 'dopey';
> +-----------+----------+-------+----------+------+---------- -----------+
> | photo_uid | username | votes | username | vote | voted_on |
> +-----------+----------+-------+----------+------+---------- -----------+
> | 0000011 | bashful | 0 | NULL | NULL | NULL |
> | 0000010 | bashful | 0 | NULL | NULL | NULL |
> | 0000005 | bashful | 0 | dopey | 1 | 2009-11-15 03:56:30 |
> | 0000003 | bashful | 0 | NULL | NULL | NULL |
> | 0000001 | bashful | 0 | NULL | NULL | NULL |
> | 0000014 | grumpy | 0 | bashful | 1 | 2009-11-15 03:48:55 |
> +-----------+----------+-------+----------+------+---------- -----------+
>
> Close, I need to also set Table_2.username != 'dopey', however the moment I
> do that, I get exactly 1 record returned:
>
> +-----------+----------+-------+----------+------+---------- -----------+
> | photo_uid | username | votes | username | vote | voted_on |
> +-----------+----------+-------+----------+------+---------- -----------+
> | 0000014 | grumpy | 0 | bashful | 1 | 2009-11-15 03:48:55 |
> +-----------+----------+-------+----------+------+---------- -----------+
>
> Not the result I was expecting. I need to keep those that say NULL in the
> username as well.
>
>
> After that, I'm stuck trying to figure out how to join the other Tables to
> get the data I need.
>
> Suggestions? I really don't want to break this into several foor loops in
> PHP, at least not if I can somehow do it all within MySQL.
>

What I don't think you understand yet is that for any rows in the
"right" table that fail to match the ON clause of a LEFT JOIN, you will
get the value NULL in every column. This is a very useful property for
detecting matches and non-matches.

Try this query instead:

select *
from Table_1
left join Table_2
ON Table_1.photo_uid = Table_2.photo_uid
AND Table_2.username = 'dopey'
where Table_1.username != 'dopey'
AND Table_2.photo_uid IS NULL;

That will list all photos from table1 not taken by dopey that match the
photo_uid from table2 also not taken by dopey

The other thing about LEFT JOINs is that as soon as you attempt to
compare against something from the "right" table (other than using "is
NULL") in the WHERE clause you force a non-null value to exist in that
table. That means that all of those missing rows (the non-matches) just
got tossed out of your potential results.

This general pattern

SELECT ...
FROM A
LEFT JOIN B
ON A.id = B.id
AND B.column = value
WHERE B.(any normally non-null column) is NULL

is the generic way to say "Show me all rows from B that match A but
don't have this value in them".

--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN

--
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

host variables

am 18.11.2009 21:44:18 von cbrown

Can someone help me. I am trying to make calls to Mysql from VBscript via=
=20ASP But I'm experiencing difficulties with passing host variables to m=
ysql
=20See examples below

isbn =3D34373
sql=3D "select isbn, book_title, publisher_name FROM cs443.books_availabl=
e where isbn=3D " &isbn

The above works but the below doesn't return any thing

Dim pubname
Pubname =3D "Bantam Books"

sql=3D "select isbn, book_title, publisher_name FROM cs443.books_availabl=
e where publisher_name =3D" &pubname

********************************************
This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.

If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.

Thank you.
********************************************

--
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