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
Special:
* speID (primary key)
* special
Bat_Spe:
* batID
* speID (both in primary key)
Tipical:
* tipID (primary key)
* tipical
Bat_Tip:
* batID
* tipID (primary key)
********************************************************************************
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 ?
********************************************************************************
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.
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
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 ?
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
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
