Problem with SQL Statement and JOIN

Hello NG,
i have 2 Tables
Table 1 -> PEOPLE_DB
Table 2 -> LINK_DB
Table1 fields: name | age | color | unique_nr|
Table2 fields: link_nr1| link_nr2 |
every row in Table1 have a lot of rows in table2
link_nr1 <=> unique_nr
link_nr2 have the unique_nr - values of other rows of table1
link_nr2 doesnt look at himselfe (link_nr2 can not be link_nr1)
WHAT I WANT TO DO :
SELECT * from People_db where age=20 and color=red and name like % ab %
where in table2 of the person is link_nr2 with value 10 AND link_nr2 with
value 44
Group By People_db.name
Order By age,color Descending
how can i realise this ?
ThanX for Help
--
Contact:
Erich Wanker | www .sec. at | wanker @. sec.at
Using:
Delphi7 | Jedi | Reportbuilder | LmdTools | Zeos | InfoPower | RXLib |
FIREBIRD 1_5 SQL
Links:
http://msdn.microsoft.com/library/default.asp
http://www.swissdelphicenter.ch/torry/
http://www.w3schools.com/sql/
http://www.delphi-fundgrube.de/
http://www.schattenbaum.net/php/Try something like this:
declare @.PEOPLE_DB table (name varchar(20), age int, color varchar(10),
unique_nr int)
declare @.LINK_DB table (link_nr1 int, link_nr2 int)
insert @.PEOPLE_DB values ('baby', 20, 'red', 1)
insert @.PEOPLE_DB values ('abbey', 20, 'red', 2)
insert @.PEOPLE_DB values ('gabby', 30, 'red', 3)
insert @.PEOPLE_DB values ('tabby', 35, 'red', 4)
insert @.LINK_DB values (1, 2)
insert @.LINK_DB values (1, 3)
insert @.LINK_DB values (1, 4)
insert @.LINK_DB values (2, 3)
insert @.LINK_DB values (2, 4)
SELECT *
from @.People_db p
inner join @.LINK_DB l
on p.unique_nr = l.link_nr1
inner join @.People_db p2
on p2.unique_nr = l.link_nr2
where p.age=20 and p.color='red' and p.name like '%ab%'
--add additional filters here
Order By p.age,p.color desc|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Your personal narrative cannot be tested.
You also need to learn that reows are not records, columns are not
fields.
If you had followed minimal netiquette and data modeling rules, would
you have actually posted something like this?
CREATE TABLE People
(person_nbr INTEGER NOT NULL PRIMARY KEY,
person_name CHAR(35) NOT NULL
birth_date DATETIME NOT NULL, -- never store age
skin_color CHAR(10) NOT NULL); -- color of what?
CREATE TABLE Partners
(person_nbr_1 INTEGER NOT NULL,
person_nbr_2 INTEGER NOT NULL,
CHECK (person_nbr_1 <> person_nbr_2)
PRIMARY KEY (person_nbr_1, person_nbr_2));
You should also have REFERENCES clauses on the Partners back to People,
but SQL Server does not allow this without triggers. Also, links are
not References. The rest of your combination narrative and personal
pseudo-code made no sense, but here is a guess.
SELECT P2.person_name
FROM People AS P1,People AS P2, Partners AS L
WHERE L.person_nbr_1 = P1.person_nbr
AND L.person_nbr_2 = P2.person_nbr
AND DATEDIFF (YR, P1.birth_date, CURRENT_TIMESTAMP) = 20
AND P1.sking_color = 'red'
AND P1.person_name LIKE '% ab %'
AND L1.person_nbr_2 IN (10, 44)
GROUP BY P2.person_name
HAVING COUNT(*) = 2;
Now, would someone else like to tell him about cross posting and
netiquette?|||On 10 Mar 2006 12:11:48 -0800, --CELKO-- wrote:
(snip)
>If you had followed minimal netiquette and data modeling rules, would
>you have actually posted something like this?
>CREATE TABLE People
>(person_nbr INTEGER NOT NULL PRIMARY KEY,
>person_name CHAR(35) NOT NULL
>birth_date DATETIME NOT NULL, -- never store age
>skin_color CHAR(10) NOT NULL); -- color of what?
>CREATE TABLE Partners
>(person_nbr_1 INTEGER NOT NULL,
>person_nbr_2 INTEGER NOT NULL,
>CHECK (person_nbr_1 <> person_nbr_2)
>PRIMARY KEY (person_nbr_1, person_nbr_2));
>You should also have REFERENCES clauses on the Partners back to People,
>but SQL Server does not allow this without triggers.
Hi Joe,
Nonsense. Check the product specs (or better: run an actual test) before
posting.
SQL Server will happily allow the foreign key constraints between
Partners and People. What it won't allow (at least not in SQL Server
2000 - I didn't check if this got fixed in SQL Server 2005) is to create
*both* these constraints with a CASCADE option. You can give the CASCADE
option to one, though; the other can only be NO ACTION (or they can both
be NO ACTION, of course).
Hugo Kornelis, SQL Server MVP