[ale] Mysql joins

[Gary Maltzen]

Easiest way is probably to create a unique user and site ID for each of
those tables; then you can use that unique ID in a match table...

CREATE TABLE USERS (
  USER_ID INTEGER UNIQUE NOT NULL,
  USER_NAME VARCHAR(8),
  REAL_NAME VARCHAR(32),
  PRIMARY KEY (USER_ID)
)

CREATE UNIQUE INDEX USER_INDEX
  ON USERS (USER_ID)

CREATE TABLE SITES (
  SITE_ID INTEGER UNIQUE NOT NULL,
  SITE_NAME VARCHAR(32)
  PRIMARY KEY (SITE_ID)
)

CREATE UNIQUE INDEX SITE_INDEX
  ON SITES (SITE_ID)

CREATE TABLE MATCH (
  USER_ID INTEGER NOT NULL,
  SITE_ID INTEGER NOT NULL
)

CREATE UNIQUE INDEX MATCH_INDEX
  ON MATCH (USER_ID, SITE_ID)

and to see the expanded list, something like

SELECT USERS.USER_NAME, REAL_NAME, SITES.SITE_NAME
  FROM USERS, SITES, MATCH
  WHERE USERS.USER_ID = MATCH.USER_ID
  AND SITES.SITE_ID = MATCH.SITE_ID

As my SQL is somewhat rusty, all bets are off on whether this will actually
work...

(The MATCH table should probably use FOREIGN KEY elements)

- beware of ironic submarines

----- Original Message -----
From: Ken N <kenn at pcintelligent.com>
To: ale at ale.org


> There's alot of good sql people here so I thought I might ask for
> some direction before I start this thing. I want to have two databases,
> One with a username and real name and two with web sites listed. See if
> there was only one table I would have to have a repeat username and
> reallname for website the person has. See my point? So I am thinking I
> will need to join from two tables.
> What i was wondering is how do I do set up the tables? I would
> need like a primary key or something? Hmm, if anyone has anysuggestions I
> am open to them cause I really haven't done much with SQL before.


--
To unsubscribe: mail majordomo at ale.org with "unsubscribe ale" in message body.