>Hi,
>
>I'm rather new to SQL (well, I had a one-year course
>at the university many years ago, but they didn't
>teach us anything practical). I did a few very simple
>data bases, but now I'm moving to something more
>serious with joins and 'group by'. The idea is to create
>a search engine for a battery distributor, specifying
>various characteristics like
>* who makes it (only one per battery)
>* special characteristics (various)
>* tipical applications (various)
>
>To simplify, I have the following tables:
>
>Battery:
>* batID (primary key)
>* makID
>* descr
>
>Maker:
>* makID (primary key)
>* mak
A battery can have only one manufacturer, so you might as well add
the mak column to Battery and remove Maker.
>Special:
>* speID (primary key)
>* special
>
>Bat_Spe:
>* batID
>* speID (both in primary key)
This is a join table. It creates a many-to-many join between Battery
and Special.
>Tipical:
Do you mean Type?
>* tipID (primary key)
>* tipical
>
>Bat_Tip:
>* batID
>* tipID (primary key)
From the SELECT statement immediately below, it appears that this is
a join table. If that is the case, then Bat_Tip.tipID should not be a
primary key. If Bat_Tip.tipID is a key, then you don't need the
Bat_Tip join table.
>*********************************************************************
>***********
>First I want to make a list of batteries with all the carateristics they
>have:
>
>SELECT M.mak, B.descr, S.special, T.tipical
>FROM Battery B, Maker M, Special S, Bat_Spe BS, Tipical T, Bat_Tip BT
>WHERE
>M.makID=B.makID AND
>B.batID=BS.batID AND BS.speID=S.speID AND
>B.batID=BT.batID AND BT.tipID=T.tipID
>
>QUESTION:
>Is there a better way of writing this join ?
Given your current schema, no.
>*********************************************************************
>***********
>Now I want to make a list of batteries which have caracteristics:
>* Special: speA
>* Tipical: tipA or tipB
>Además I want to sort the result such that if a battery has
>both tipA _and_ tipB, it comes first. I am thinking of doing it
>with temporary tables:
>
>tmp_spe:
>* batID (primary key)
>* count
>
>tmp_tip:
>* batID (primary key)
>* count
>
>INSERT into tmp_spe
>SELECT batID, count(speID)
>FROM Bat_Spe
>WHERE (speID='speA')
>GROUP BY batID
>
>QUESTION:
>Is there any difference between
>* SELECT batID, count(speID)
>* SELECT batID, count(batID)
>* SELECT batID, count(*) ?
>In this case, the three work and return the same result.
In the future, if you decide to change the WHERE clause to search for
two or more speIDs (e.g. WHERE speID = 'speA' OR speID = 'speB'),
then the counts will be different. Decide what you actually want to
count; speIDs, batteries, or rows. Even if the WHERE clause doesn't
change, what you count gives you a clue as to what the statement
does. Suppose you want to count rows and you use count(batID). Six
months from now when you've forgotten how this works, the use of
count(batID) will make it more difficult to figure out that you are
counting rows.
>INSERT into tmp_tip
>SELECT batID, count(tipID)
>FROM Bat_Tip
>WHERE (tipID='tipA' OR tipID='tipB')
>GROUP BY batID
>
>SELECT B.batID, (TS.count+TT.count)
>FROM Battery B, tmp_spe TS, tmp_tip TT
>WHERE B.batID=TS.batID AND B.batID=TT.batID
>ORDER BY 2 DESC
I believe that's a fluke. Since you are ordering by (TS.count +
TT.count), a battery with speA and tipA can appear ahead of a battery
with tipA and tipB. In order to avoid this, use
ORDER BY TT.count DESC
You can combine the two ORDER BY conditions to order within TT.count.
ORDER BY 2 DESC, TT.count DESC
>This returns all the batteries which have all the
>characteristics I want, ordered as I want.
>
>QUESTION:
>Well, how does it sound ? Is there a better way to
>do it ?
>
Your method looks good to me. Pretty impressive for someone who
studied SQL many years ago for a period of one year.
>QUESTION:
>Now, I would need to get, for each battery matched,
>a list of all the characteristics it has. That is, if
>battery with batID='bat1' has tipical applications
>'tipA', 'tipB' and 'tipC', can I retrieve in one shot
>all the batteries matched, together with the
>characteristics each one has,
>_or_ do I have to go through more temporary tables,
>_or_ is it better to make one query for each battery I have
Join the last statement above to the Bat_Tip and Tipical tables.
SELECT B.batID, T.tipical
FROM Battery B, tmp_spe TS, tmp_tip TT, Bat_Tip BT, Tipical T
WHERE B.batID=TS.batID AND B.batID=TT.batID
AND B.batID = BT.batID AND BT.tipID = T.tipID
You can join to the Special table in the same way.
>Well, I know that's a bit long, but it would greatly
>help to orientate me on how to continue...
>
>Thanks in advance for any help or hint,
>
>Olivier
All SQL was processed in the MySQL server in my brain, not the one on
my computer. Expect syntax errors.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database
---------------------------------------------------------------------
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
