Looking for the opposite of this Join.

chadu

I'm a bit of a newbie in the database area... I often have a DB admin type guy to help me muddle through DB powered sites, but alas, in this one, I'm all alone.

I was able to write a join that gets the results I want for one table, and now I want to display all rows that dont meet that criteria... here is the join, can anyone help me write the opposite?

SELECT * FROM approve LEFT JOIN storyboard ON STORYBOARDID = SCREENNUMBER

I bet this is easy stuff for the DB admins out here but I guess Im just not there yet.

Thanks in advance for any pointers or help.

LordShryku

You could just swap the join. Or you could do a right join instead

SELECT    * 
FROM      storyboard
LEFT JOIN approve  

ON        SCREENNUMBER = STORYBOARDID

SELECT     * 
FROM       approve 
RIGHT JOIN storyboard 
ON         STORYBOARDID = SCREENNUMBER

chadu

I tried both of those, they bring back all of the records, not the opposite records of my other join.

Any ideas on what is going on?

Danicek

What do you mean with

all rows that dont meet that criteria

chadu

A little more back ground on my tables...

I have a table called storyboard...

ID int(3) NOT NULL auto_increment,
TITLE text NOT NULL,
SECTION text NOT NULL,
SUBSECTION text NOT NULL,
ASSETNAME varchar(24) NOT NULL default '',
DESCRIPTION text NOT NULL,
SCREENNUMBER int(3) NOT NULL default '0',
ORDERINSET int(3) NOT NULL default '0',
TOTALINSET int(3) NOT NULL default '0',
UNIQUE KEY ID (ID),
UNIQUE KEY screenNumber (SCREENNUMBER)

I have a table called APPROVE...

ID int(3) NOT NULL auto_increment,
UNAME varchar(24) NOT NULL default '',
STORYBOARDID int(3) NOT NULL default '0',
COMMENTS text NOT NULL,
DATE timestamp(14) NOT NULL,
SEVERITY enum('low','medium','high') NOT NULL default 'low',

And when a user reviews a storyboard, a new row gets inserted into "APPROVE". The first join brings back all rows that have been approved. I am looking for all rows in sotryboard that dont have a corresponding row in STORYBOARD.

Does that help?

LordShryku

Do you mean "don't have a corresponding row in Approve"? If so, it's just a small addition to your first query. The first one brings back all rows from storyboard, regardless if they are in approve or not. And since you aren't selecting anything from approve, the join is rather useless. But anyway, if this is what you mean, just add a NULL clause to the query like so

SELECT    * 
FROM      storyboard
LEFT JOIN approve  

ON        SCREENNUMBER = STORYBOARDID
AND       approve.ID IS NULL

chadu

LordShryku, that is what I meant, sorry for the error in that last post.

Thanks for yur input, but that doesn't work either.

As for my first query, yes I am using information from Approve... I echo out the timestamp and use the column "STORYBOARDID" to determine what row to echo it out in...

my first query brings back a table like so:

<table width="750" border="0">
<tr bgcolor="#666666" class="boldcopy">
<td width="150">DATE REVIEWED </td>
<td>TITLE</td>
<td>SECTION</td>
<td>SUBSECTION</td>
<td>SCREENNUMBER</td>
</tr>
<tr bgcolor="#CCCCCC" class="copy">
<td width="150">11/26/2003</td>
<td>Safety</td>
<td>Introduction</td>
<td>Safety is ongoing commitment</td>
<td>2</td>
</tr>
<tr bgcolor="#CCCCCC" class="copy">
<td width="150">11/26/2003</td>
<td>Safety</td>
<td>Introduction</td>
<td>Training</td>
<td>3</td>
</tr>
</table>

My second query should bring back the one row in storyboard that hasn't been reviewed and does not yet have a row in approve.

LordShryku

I'm gettin myself confused here.

What about the query I gave you "doesn't work"? Does it return an error? Is it not returning the desired results? What results is it returning?

And what I meant about the useless join...

SELECT FROM approve LEFT JOIN storyboard ON STORYBOARDID = SCREENNUMBER

What I meant from before, was using a left join on storyboard, but not pulling anything from it makes it a useless join. I'm actually suprised this doesn't just error out because you have the ambiguous . You never tell is which table to get * from.

chadu

It returns all the rows in STORYOARD.

No mysql error, just the undesired results.

LordShryku

Ok, ok, ok. You want all rows in storyboard that don't exist in approve. The AND should have been a WHERE. And taking out all of the ambiguity, this should do it. Otherwise, I'm just a dumbass

SELECT    s.*, a.ID
FROM      storyboard s
LEFT JOIN approve a
ON        s.SCREENNUMBER = a.STORYBOARDID
WHERE     a.ID IS NULL

chadu

That did it.

LordShryku, thank you!

So as to avoid having this issue in the future, does anyone know a good SQl theory /refernce book on this type of information?

MySQL.com is a good start, but I just want something more about general relational database design.