Author |
Message |
Satis
Felix Rex
Joined: Fri Mar 28, 2003 6:01 pm Posts: 16646 Location: On a slope
|
SQL question: JOINs and Many-to-one
ok...I've been racking my brain on this and am stuck. I know it's possible (anything's possible).
I'm trying to join two tables. The primary table has a list of data I want. The second table has the potential of 1+ relationships with the secondary table. I want to grab data from the primary table only, but only if the most recent row from the second table contains a field with a specific value. I know how convoluted that sounds, so a bit of sql:
SELECT a.ID, a.Data, a.Whatever
FROM master a, sub b
WHERE a.ID = b.ID
AND b.bugger = 'Satis'
That works...except if Satis appears in multiple rows in tabe b I get multiple returns for the same row in the master table. Ordinarily I could fix that with GROUP BY, but Whatever is a text field, so no grouping.
I kinda worked around it with
SELECT a.ID, a.Data, a.Whatever
FROM master a
WHERE (SELECT count(*) FROM sub WHERE ID=a.ID AND bugger='Satis') > 0
...but that will return what I want if Satis is anywhere in that table for that id, even he's not the most recent entry.
So...anyway...am I missing something obvious here? Can I do this in a single query or do I need to do multiple queries?
_________________ They who can give up essential liberty to obtain a little temporary safety, deserve neither liberty nor safety.
|
Thu Jul 26, 2007 8:03 am |
|
|
Satis
Felix Rex
Joined: Fri Mar 28, 2003 6:01 pm Posts: 16646 Location: On a slope
|
ok...I solved it...kinda...but it's friggin' ugly.
SELECT a.ID, a.Data, a.Whatever
FROM master a
WHERE EXISTS
(SELECT * FROM sub WHERE ID=a.ID and bugger='Satis' AND order=
(SELECT max(order) FROM sub WHERE ID=a.ID))
man...my sub query has a sub query. But it works. I would LOVE to hear a better option. Oh...and order is like a numerical order....I could've used a unique auto number id too.
_________________ They who can give up essential liberty to obtain a little temporary safety, deserve neither liberty nor safety.
|
Thu Jul 26, 2007 8:22 am |
|
|
Pig
Duke
Joined: Mon Mar 31, 2003 8:59 am Posts: 1358 Location: right behind you
|
You want to use an inner join, but you need to specify the criteria for the join in the join. In other words, if you want to specify criteria for your join, you cannot do it in the where clause. The join is already completed by that point. You need to put the join in the JOIN syntax. I think it looks like this.
I have never tried to put a function in there, but I don't see why it wouldn't work. I have done things like: INNER JOIN t2 ON (t1.2id = t2.id AND t2.foo > 0 )
|
Fri Jul 27, 2007 6:01 pm |
|
|
Satis
Felix Rex
Joined: Fri Mar 28, 2003 6:01 pm Posts: 16646 Location: On a slope
|
Thanks brother man. I'll give it a shot on Monday.
how's the rug puppy?
_________________ They who can give up essential liberty to obtain a little temporary safety, deserve neither liberty nor safety.
|
Sat Jul 28, 2007 9:45 am |
|
|
Pig
Duke
Joined: Mon Mar 31, 2003 8:59 am Posts: 1358 Location: right behind you
|
wtf is a rug puppy?
|
Sat Jul 28, 2007 1:40 pm |
|
|
Satis
Felix Rex
Joined: Fri Mar 28, 2003 6:01 pm Posts: 16646 Location: On a slope
|
well, rug rat is an overused phrase, so I coined a new one.
_________________ They who can give up essential liberty to obtain a little temporary safety, deserve neither liberty nor safety.
|
Sun Jul 29, 2007 8:23 am |
|
|
Pig
Duke
Joined: Mon Mar 31, 2003 8:59 am Posts: 1358 Location: right behind you
|
Sounded kind of lewd, and since it came from you that seemed to clinch it...
She is good. Today she learned how to play risk. Not my favorite game in the world, but it's the first strategy game she has been interested in and been able to understand.
|
Sun Jul 29, 2007 10:54 pm |
|
|
Satis
Felix Rex
Joined: Fri Mar 28, 2003 6:01 pm Posts: 16646 Location: On a slope
|
That's cool. Risk is a good game. Perhaps not quite as intricate as I would like, but fun none-the-less. I get a deep thrill when I invade Canada and imagine my soldiers slaughtering the inhabitants.
_________________ They who can give up essential liberty to obtain a little temporary safety, deserve neither liberty nor safety.
|
Mon Jul 30, 2007 6:29 am |
|
|
Satis
Felix Rex
Joined: Fri Mar 28, 2003 6:01 pm Posts: 16646 Location: On a slope
|
ok...punked around with the inner join. Afraid no go, but I may have miscommunicated the table schema.
table 1:
PrimaryID
data1
data2
data3
table 2:
PrimaryID
user
Date record was written
sequence number
So a typical 'record' would be
table1: (only one record)
123456
a status code
Some random text
Some more text
table2: (one or more records)
123456 (Record one)
satis
1-1-1980
66
123456 (Record two)
some other yahoo
1-2-1980
67
So basically I'd want to retrieve dataq from table 1, but only if the most recent entry in table2 (on the primary ID field) had me in it. Your inner join from above had the same effect as my original one.
_________________ They who can give up essential liberty to obtain a little temporary safety, deserve neither liberty nor safety.
|
Mon Jul 30, 2007 6:51 am |
|
|
Pig
Duke
Joined: Mon Mar 31, 2003 8:59 am Posts: 1358 Location: right behind you
|
Hmm... Yeah, what I posted was wrong. I misunderstood. I don't think you can do that without sub queries. You might be able to make it a little cleaner, but I wouldn't bother.
|
Mon Jul 30, 2007 1:15 pm |
|
|
Satis
Felix Rex
Joined: Fri Mar 28, 2003 6:01 pm Posts: 16646 Location: On a slope
|
oh hell, I was afraid you might say that. That's just ugly. I don't even know how to do a count... bleh, I'm sure I could figure it out. It's just much more complicated than it needs to be (imo). I'd probably be better off just retrieving the whole list and then running through it in code and tossing duplicates.
Anyway, thanks for the help piggarooni.
BTW, I can't connect to Jabber/GTalk any more but I'm still on MSN. Say hi sometime if youwant.
_________________ They who can give up essential liberty to obtain a little temporary safety, deserve neither liberty nor safety.
|
Tue Jul 31, 2007 2:59 pm |
|
|
Pig
Duke
Joined: Mon Mar 31, 2003 8:59 am Posts: 1358 Location: right behind you
|
I'll add you to my work account if I remember.
|
Tue Jul 31, 2007 11:20 pm |
|
|