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