2

SQL newbie here. I have the following schema.

I am to provide a list of all active primary super charged domains belonging to the foobar association.

As far as I know, three tables (sites, domain, and name) cannot be joined together. How can this be done?

Steve C
  • 527
  • 1
  • 3
  • 14
  • 1
    You can "chain" joins/ _...FROM a INNER JOIN b ON [condition1] INNER JOIN c ON [condition2]_ – Uueerdo Apr 19 '16 at 21:04
  • 1
    of course you can join three or more tables! – SQL Police Apr 19 '16 at 21:04
  • You most certainly CAN join 3 or more tables together. You just need to have something to join them on...from the looks of what you've provided, I'm not certain that this can be done with the schema you've provided, as the fields seem to differ between the tables (unless id is actually what's used to link them, I'd guess you might be missing some sort of grouping tables that would sit between these) – user2366842 Apr 19 '16 at 21:09
  • This may help you http://stackoverflow.com/questions/12719266/how-to-join-two-unrelated-tables-in-sql – Garry Apr 19 '16 at 21:09
  • Since your diagram shows a FK relationship for each of these tables of course they can be joined by definition if you have a PK/FK relationship you can join. Why did you think you could not? – HLGEM Apr 19 '16 at 21:10

1 Answers1

2

This is an example of joining three tables

SELECT * FROM companies
JOIN sites ON sites.company = companies.id
JOIN domains ON domains.site = sites.id
WHERE 
    sites.is_supercharged = 1 AND sites.is_deleted = 0 AND 
    domains.is_primary = 1 AND domains.is_deleted = 0

It based on the schema (I hope the field names are correct, the text is quite small)

When you joining a table to a query, you need to specify the matching fields reference in the ON part. And then you can add any condition in the WHERE part.

Alon Eitan
  • 11,997
  • 8
  • 49
  • 58
  • Since the naming convention is so very poor, the OP should actual script out what the PK/FK relationships are to know what fields to join on. I agree your choices seem likely for all but the join between Domain and Site. This is prime example of why using Id as a PK is bad. It is not obvious what the join fields are. But since the diagram has relationships, I am sure they are there somewhere. Scripting them out is the surest way to be sure you are joining on the correct fields. – HLGEM Apr 19 '16 at 21:15
  • But the red ones, aren't they represent the foreign keys to the other tables? I do hope that if i'm wrong, then the OP will at least get the general concept of joining tables. – Alon Eitan Apr 19 '16 at 21:20
  • Yeah I think you are probably right, but he needs to make sure rather than guessing. I am afraid those little color changes were too difficult for my old bifocal eyes to see until you pointed them out! – HLGEM Apr 19 '16 at 21:25
  • @HLGEM I wouldn't say the issue in this case was that `id` was used, but that the referencing fields did not follow the `[tablename]_id` convention. – Uueerdo Apr 20 '16 at 16:20
  • @Uueerdo, I woudl suggest you read the book on SQL antipatterns to get educated on why ID is a SQL Antipattern. http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557 – HLGEM Apr 20 '16 at 17:23
  • @HLGEM I'm not going to buy a book to argue your case, but a cursory web search seems to suggest the antipattern you are talking about is assigning a "handle id" to every table as it's primary key. However, a "pragmatic" programmer usually tries to avoid a bunch of foreign keys on the string value(s) that normally form "natural" (and possibly composite) primary keys; especially when InnoDB clusters on the primary key. Such natural keys become even less desirable when changes to business needs suddenly render the previous value or combination of values forming the primary key non-unique. – Uueerdo Apr 20 '16 at 18:23
  • In fact, the first paragraph of your answer [here](http://stackoverflow.com/questions/141126/what-is-important-to-keep-in-mind-when-designing-a-database/141333#141333) seems to address this – Uueerdo Apr 20 '16 at 18:31
  • I didn't say don't have autogenerated numeric ids, I said they should never be named ID. I almost never use a "natural key" for just the reason you stated. – HLGEM Apr 20 '16 at 20:57
  • It was unclear the exact nature of the anti-pattern you were suggesting; while `id` is not the most descriptive, I would hardly call it an antipattern (especially, if as I suggested earlier, the standard that all referencing fields use a form like `[tablename]_id`). – Uueerdo Apr 20 '16 at 22:23