SQL Cross Table Linking

RMS Oceanic

I have two tables in a single database, of the structure

Table A
{value a, value b, value c, value d, value e, value f, value g, value h, value i}

Table B
{value a, value b, value c, value d, value j, value k, value l}

The event these tables log is a single event, i.e. every time the event happens, an entry is appended to both A and B.

Now I have a stored procedure, currently of the structure:

select a, c, f, i from A
where a = blah
and b = blah
and e = blah

I want to rewrite it so it filters what it returns from A depending on what its counterpart entry in B says, namely I only get results where in B, l equals blah, so we have:

select A.a, A.c, A.f, A.i from A, B
where A.a = B.a
and A.b = B.b
and A.c = B.c
and A.d = B.d
and A.a = blah
and A.b = blah
and A,e = blah
and B.l = blah

In other tables I've crosslinked in the past, there have been enough shared variables or a common primary key that this method definately returns a single entry from A for each event that logged blah into B's l. However the values for these table's particular links are a little less specific, so ultimately my question is: What is the best way to avoid getting duplicate returns from this query?

Dr. Frenchenstein

A join i would think, do the tables have a common unique field?

exmello

You're probably not finding anything in your google searches because of your term "crosslinking". Look into joins, inner join, left join, etc. The statements you are making are doing an implicit join, and you want to make this explicit

select A.a, A.c, A.f, A.i 
from A
inner join B
on A.a = B.a
and A.b = B.b
and A.c = B.c
and A.d = B.d
where A.a = blah
and A.b = blah
and A,e = blah
and B.l = blah

Gilbert0

A join is just querying from multiple tables. Even though he doesn't have the "Join" keyword, he has an implicit equivalent join. That's what the a.a = b.a is.

You only want results once? Look into the "distinct" keywork in the select section.

http://www.w3schools.com/sql/sql_distinct.asp

But I think what you ultimatly want is a outer join. This way you don't have to have data in both columns.

http://www.w3schools.com/sql/sql_join_left.asp

edit - what exmello said. Look into the different join types.

bowen

Keep in mind a lot of modern query languages will decide and perform joins for you automatically.

Distinct, like glibert0 says, might be something you want, outer join is the other possibility too. W3schools (ugh) has a relatively nice set of sql examples.

Dr. Frenchenstein

whats wrong with W3schools? i use that site a lot for syntax questions.

bowen

Dr. Frenchenstein wrote:

whats wrong with W3schools? i use that site a lot for syntax questions.

They've been known in the past to be incorrect on some key examples and refuse to correct them (or maybe just slow about correcting them). They've got some pretty good tutorials though, that's for sure.

Dr. Frenchenstein

good to know!

RMS Oceanic

I think I need to give a little more information:

On the SQL tables I previously joined, there was no unique ID, and this is true here as well. Instead the four variables I used to isolate a single incident was:

- The date it happened
- The time it happened
- The name of the server it happened on
- The port number in the server it happened on

In this table, instead of the time, I have the name of the physical piece of specialist hardware the port number was called from. If three occurences on the same server on the same hardware on the same port number happened on the same date where "l" was "blah" in all three cases, would joining the tables guarantee I only get three results and not any repeats?

exmello

Maybe, maybe not. Try it out, use distinct if you have duplicates.

bowen

Keys don't need to be unique, composite data can be unique, which is what DISTINCT is there to do. I can use a foreign key and have it never be unique.

I'm convinced you want DISTINCT there.

Infidel

Can you not you add a key that is shared by both?

Infidel

bowen wrote:

Keys don't need to be unique, composite data can be unique, which is what DISTINCT is there to do. I can use a foreign key and have it never be unique.

I'm convinced you want DISTINCT there.

Keys have to be unique. A foreign key is unique, it just is referenced multiple times in the referencing table.

AngelHedgie

The solution is to use distinct (which tosses repeat results) with an explicit inner join of the two tables on the common elements plus the value on B that you are looking for. (I'll put up a code example later, when I'm not on my tablet.)

SeñorAmor

Just so I'm clear here, you want to pull ALL records from B (that match where B.l == 'blah') and then the matching records from A where A.[a|b|c|d] == B.[a|b|c|d], yes?


*edit*
Maybe you can put up some fake db entries and what your expected results will be.

bowen

Infidel wrote:

bowen wrote:

Keys don't need to be unique, composite data can be unique, which is what DISTINCT is there to do. I can use a foreign key and have it never be unique.

I'm convinced you want DISTINCT there.

Keys have to be unique. A foreign key is unique, it just is referenced multiple times in the referencing table.

Well, that is what I meant by that. Apologies.

Chickeen

Could be a candidate for group by and having clauses, if you're looking for the the same "event" occurring at least N number of times.

Grobian

Dr. Frenchenstein wrote:

whats wrong with W3schools? i use that site a lot for syntax questions.

This is slightly offtopic, but there's a whole website on that question.

AngelHedgie

Okay, I promised code:

SELECT DISTINCT A.a, A.c, A.f, A.i 
FROM          A
INNER JOIN  B
ON  A.a = B.a
AND A.b = B.b
AND A.c = B.c
AND A.d = B.d
AND B.l = blah   --This one's a bit important to explain - since we only want to see those pairings where B.l is blah, we put it in the join logic to limit what comes back.
WHERE A.a = blah
AND A.b = blah
AND A.e = blah