It is currently Thu Mar 28, 2024 1:59 pm



Reply to topic  [ 12 posts ] 
SQL question: JOINs and Many-to-one 
Author Message
Felix Rex
User avatar

Joined: Fri Mar 28, 2003 6:01 pm
Posts: 16646
Location: On a slope
Reply with quote
Post 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
Profile WWW
Felix Rex
User avatar

Joined: Fri Mar 28, 2003 6:01 pm
Posts: 16646
Location: On a slope
Reply with quote
Post 
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
Profile WWW
Duke
User avatar

Joined: Mon Mar 31, 2003 8:59 am
Posts: 1358
Location: right behind you
Reply with quote
Post 
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.

Code:
SELECT
   *
FROM
   table1 t1,
   table2 t2
INNER JOIN t2 ON (
   t1.t2id = MAX(t2.id)
)
WHERE
   t1.bugger = 'Satis';

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
Profile YIM WWW
Felix Rex
User avatar

Joined: Fri Mar 28, 2003 6:01 pm
Posts: 16646
Location: On a slope
Reply with quote
Post 
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
Profile WWW
Duke
User avatar

Joined: Mon Mar 31, 2003 8:59 am
Posts: 1358
Location: right behind you
Reply with quote
Post 
wtf is a rug puppy?


Sat Jul 28, 2007 1:40 pm
Profile YIM WWW
Felix Rex
User avatar

Joined: Fri Mar 28, 2003 6:01 pm
Posts: 16646
Location: On a slope
Reply with quote
Post 
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
Profile WWW
Duke
User avatar

Joined: Mon Mar 31, 2003 8:59 am
Posts: 1358
Location: right behind you
Reply with quote
Post 
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
Profile YIM WWW
Felix Rex
User avatar

Joined: Fri Mar 28, 2003 6:01 pm
Posts: 16646
Location: On a slope
Reply with quote
Post 
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
Profile WWW
Felix Rex
User avatar

Joined: Fri Mar 28, 2003 6:01 pm
Posts: 16646
Location: On a slope
Reply with quote
Post 
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
Profile WWW
Duke
User avatar

Joined: Mon Mar 31, 2003 8:59 am
Posts: 1358
Location: right behind you
Reply with quote
Post 
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
Profile YIM WWW
Felix Rex
User avatar

Joined: Fri Mar 28, 2003 6:01 pm
Posts: 16646
Location: On a slope
Reply with quote
Post 
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
Profile WWW
Duke
User avatar

Joined: Mon Mar 31, 2003 8:59 am
Posts: 1358
Location: right behind you
Reply with quote
Post 
I'll add you to my work account if I remember.


Tue Jul 31, 2007 11:20 pm
Profile YIM WWW
Display posts from previous:  Sort by  
Reply to topic   [ 12 posts ] 

Who is online

Users browsing this forum: No registered users and 7 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group.
Designed by STSoftware.