On Tue, Feb 12, 2002 at 01:31:43PM -0000, DL Neil wrote:
| Hello Nuno,
|
| > I'm trying to make a query with only one command but I can't see how to do
| > it.
| >
| > Supose that are 2 tables like:
| >
| > table QA table QB
| > ref text ref text
| > --- ---- --- ----
| > a1 texta1 b1 textb1
| > a2 texta2 b2 textb2
| > a3 texta3 b3 textb3
| >
| > and there is a table that indicates the relation between QB and QA data:
| >
| > table relAB
| > A B
| > -- --
| > a1 b2
| > a1 b3
| > a1 b4
| > a2 b1
| > a2 b2
| >
| > This example says that b2,b3,b4 are related to a1 and b1,b2 are
| > related to a2.
| >
| > What I want to do is get a list from table QB related to an item from
| > table QA:
| >
| > For example, get a list from QB related to item 'a1':
| >
| > 1. select B from relAB where A='a1';
| >
| > (result: b2, b3, b4)
| >
| > 2. select * from QB where ref in ('b2','b3','b4');
| >
| > (result: only 'a1' related items)
| >
| > There is a way of make this query with only one command?
| >
| > Something like:
| >
| > 1. select * from QB where ref in (select B from relAB where A='a1');
|
|
| The technique you describe is called a "join", ie joining two tables together, and
|yes, you can have more than
| one join in a single SQL query.
|
| So starting with (1) let's join tables RelAB and QA by informing MySQL that we want
|to match the row in table QA
| with the corresponding row(s) in RelAB
|
| select A, B from relAB, QA where QA.ref='a1' AND QA.ref=A;
|
| (notice that I added "A" to the SELECT clause, which we know from the WHERE clause
|will be 'a1', but I assume
| that once you have the query working that query-value change from time-to-time. Also
|I moved the relationship
| between the constant ('a1') and the source-table. If the tblNm.colNm notation is
|unfamiliar, please check the
| manual - it is necessary because the term "ref" is ambiguous/could refer to either
|tbl QA or tbl QB)
|
| Similarly with (2) we can extend your code to join tables RelAB and WB
|
| select * from QB where QB.ref in ('b2','b3','b4') AND B=QB.ref;
|
| NB let's treat this as pseudo-code that only looks like SQL!
|
| Of course you found the 'b2','b3','b4' series by executing query (1), so let's now
|put the two queries
| together - just as you knew they should be:
|
| select A, QB.* from QB, relAB, QA
| where QA.ref='a1'
| AND QA.ref=A
| AND B=QB.ref;
|
| Now that you know the terminology, you will be able to find helpful tutorials on the
|various supporting web
| sites - start with MySQL's site.
|
| BTW: I followed your sequence of queries to construct the joined-query. Perhaps it's
|just me, but I normally
| start at the 'end' and work 'back' to the 'beginning' - so I'd start with what I
|wanted out of QB, work back to
| 'how to get it from relAB' and thence right back to QA. I find it easier to
|'visualise', but then other people
| aren't (quite) this crazy!
|
| Regards,
| =dn
Hi,
Ok. I start understand this better and what I need is working very well now.
I think I've posted this doubt because I made some confusion about "linking"
or relating diferent tables like in this example.
Now I know that with JOIN I can create relations between diferent tables and
then maintain a good database structure by having things separated from each
other.
Thanks again for you help,
--
Nuno Teixeira
pt-quorum.com
/*
PGP Public Key:
http://www.pt-quorum.com/pgp/nunoteixeira.asc
Key fingerprint:
8C2C B364 D4DC 0C92 56F5 CE6F 8F07 720A 63A0 4FC7
*/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
