SQL Guru?

Jim

Does someone know a SQL guru who might help me understand putting together a multi-table query?

todd burch

Maybe. But I've only been working with SQL since 1984.

Jim

Thanks, maybe. I constructed a query that seems to work. I first assumed I needed multiple JOINs, but my solution isn't using any JOINs. It selects fields from 3 tables and uses the WHERE clause to match rows on certain conditions. Is there such a thing as an implied JOIN?

todd burch

You can have a join without specifying the JOIN keyword. For instance,

SELECT A.SSN, A.NAME, B.ADDRESS FROM TABLEA A, TABLEB B WHERE A.SSN = B.SSN

That's a join. Lots of other ways to do it.

Jim

That is what I did, but now have another one.

I have a table for plugins and one for authors. Some plugins have multiple authors, so I need to get all the authors for each plugin as a single record. I might guess I need a third table to define the relationship between plugins and authors, but I have no idea if that's right; nor how to write the query.

todd burch

I believe to get a single record, you'll have to have an ugly setup where your third table looks something like:


Plugins          Authors
---------- --------------------------------------------------
abc.rb           Jim, Todd, Clark  
def.rb            Jim, Chris, Matt

thomthom

I've done something similar. For my website I've added tags for my blog and gallery entries. I have a table with all the unique tags, then I have a table which links the blog or gallery item to the tags.

To get a list of all blog entries and the tags for each blog entry I do a SQL query to get a list of all blog items.
Then afterwards, for each blog item I do another SQL query that fetches all the tags related to that item. I've not been able to do it any other way.

So I think I'd do something like this:


Users Table
==========
userID   name   website   ...
   0     "Bob"
   1     "Jim"
   2     "Jon"


Plugin Table
==========
pluginID   title   ...
    0      "Foo"
    1      "Bar"


Plugin Authors Table
==========
rowID   pluginID   userID
  0         1         5
  1         1         2
  2         1         4
  3         2         5
  4         3         7

So when listing the plugins and their authors, I'd do one Query to the Plugin Table. Then for each row returned, I'd query the Plugin Authors Table which matches the pluginID joining the userID with the User Table.

Jim

Ok, thanks T & Tt. I understand so far. I have tables very similar to the examples, with the exception of the Authors_Plugins table. I wasn't sure if that was a good design choice or not, nor do I have any idea how to get the information out as a single record.